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

This works for me.

Great fun, now back to work!

with prob01a as
(select replace(field04, '$', '') as money,

case right(trim(field04), 1) when '-' then -1 else 1 end as factor
from prob01)

select replace(money, '-', '') as realmoney,
money, case money when ' ' then 0 else
decimal(replace(money, '-', ''), 9, 2, '.') * factor end
FROM prob01a

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Alan Shore
Envoyé : jeudi 22 janvier 2009 21:22
À : Midrange Systems Technical Discussion
Objet : Re: SQL String to numeric conversion

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

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
t> --> (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> -
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.