Re: SQL String to numeric conversion -- MIDRANGE-L

I think given the different kinds of data and the kind of problems you
are facing, I think this would be a good time to look at a SQL User
Defined Function(UDF).

With the UDF you could pass the data to a RPGILE data scrubber and get
back a clean result. Given the kinds of problem you are facing, I
doubt you will ever come up with SQL code that could deal with it all.
The other advantage of using the UDF is that if new kinds of garbage
comes in, you add code to one place to fix the problem. Not try to fix
many SQL statements.

Pretty simple UDF. Just receive a VarChar and return a packed 11/2. As
always you will find an example of a UDF at
www.think400.dk/downloads.htm under iDate.

On Thu, Jan 22, 2009 at 1:21 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Afternoon all
I took some of the responses answering the original request because I have
somewhat of the same problem.
I have a file (PROB01) containing the following data in a particular field
(Field04)
Field04

$
$302.54
3.66
1.54-
33.60-
$ 11.21-
2.22
.34
$ .36
$11

Using the following sql statement
with prob01a as
(select replace(field04, '$', '') as money from prob01)
select money, case money when ' ' then 0 else
decimal(money,9,2,'.') end
FROM prob01a
I get the following results
MONEY CASE expression
.00
.00
302.54 302.54
3.66 3.66
1.54- +++++++++++++
33.60- +++++++++++++
11.21- +++++++++++++
2.22 2.22
.34 .34
.36 .36
11 11.00

as you can see the problem I am having is with the minus sign, and for the
life of me I just cannot seem to come up with the correct solution to
satisfy all conditions
Anyone any ideas?

Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 01/22/2009 09:41:29 AM:

David,

The data that I tryied to send along is from a client's print to file
statements. The records that we are working against are from the total

boxes

on the statement. In the data field in question, there are 5 separate

total

fields in an alpha field 100 bytes in length.

The SQL statement I had included with my attempt to share a few of the
records seems to be doing the trick. While it gives us 2 totals, it works

for our purposes since the amounts are always in the same location. We

just

change the byte location in the statement for the other 4 total fields.

select sum(decimal(replace((substring(fldname,1,17)),'$',''),9,2,'.'))
FROMfilename WHERE recid = '3' GROUP BY substring(fldname,18,1)

The recid field contains the area of the statement that the data was
located.

However, you did present some interesting ideas. I have not explored the
"locate" usage as of yet. I can think of a couple other opportunities for

something like that. Thank you for that.

Nick Mart

----- Original Message -----
From: "David FOXWELL" <David.FOXWELL@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, January 22, 2009 1:36 AM
Subject: RE: SQL String to numeric conversion

From the RPG list

I had a go at this :

My sample data : ( you didn't give exact information )
select $ from dollars
....+....1
$
$ 302.54
$1.59-
$33.60-

My statement :
with mytable as (
SELECT length(trim($)), locate('.', trim($)), substr(trim($), 2,
locate('.', trim($))-2) as dollars , substr(trim($), locate('.',
trim($))+1, 2) as cents , right(trim($), 1) as sign FROM dollars
)

select case when sign ='-' then dollars concat cents
*-1 else dollars concat cents end as cents
from mytable

Result is a list in cents :
...1....
CENTS
30.254
159-
3.360-

I didn't want to go further because we need more details about your

data.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

De la part de Nick W Mart
Envoyé : mercredi 21 janvier 2009 16:21
À : RPG programming on the IBM i / System i
Objet : Re: SQL String to numeric conversion

I have more information regarding my previous question. Here is a

sample

of the actual data field.

....+....1....+....2....+....3....+....4....+....5....+....6....

+....7....+....8....+....9....+...

INFO TO PRINT $1.59- $.00 $1.59-
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $33.60- $.00 $33.60-
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 !
$.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00We came up with the following SQL statement that gave us 2
totals.select
sum(decimal(replace((substring(fldname,1,17)),'$',''),9,2,'.'))
FROMfilename WHERE recid = '3' GROUP BY substring(fldname,18,1)If there

is

something that would be better, we would appreciate thesuggestions.Nick

Mart----- Original Message -----From: "Nick W Mart"
<nickmart@xxxxxxxxxxxxxxx>To: "RPG programming on the AS400 / iSeries"
<rpg400-l@xxxxxxxxxxxx>Sent: Wednesday, January 21, 2009 8:07

AMSubject:

SQL String to numeric conversion> If I have a value of "$38.54-" in a

text

field is there a way that youknow of in sql so that I can do a sum on

all

records?>> Thank you in advance.>> Nick Mar!
t> --> This is the RPG programming on the IBM i / System i (RPG400-L)
mailinglist> To post a message email: RPG400-L@xxxxxxxxxxxx> To
mailinglist> subscribe, unsubscribe, or change list options,> visit:
mailinglist> http://lists.midrange.com/mailman/listinfo/rpg400-l> or
mailinglist> email: RPG400-L-request@xxxxxxxxxxxx> Before posting,
mailinglist> please take a moment to review the archives> at
mailinglist>

http://archive.midrange.com/rpg400-l.>---------------------

mailinglist>

-----------------------------------------------------------

mailinglist> No virus found in this incoming message.Checked by AVG -
mailinglist> http://www.avg.comVersion: 8.0.176 / Virus Database:
mailinglist> 270.10.10/1905 - Release Date: 1/20/20092:34 PM
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--------------------------------------------------------------------------------

No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.10.12/1908 - Release Date:

1/21/2009

9:15 PM

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.