RE: sql on flat file

You can use DEC and SUBSTR to get out what you want separately. For example
to get the conversion rate with the decimal in the place you want it:

SELECT (DEC(SUBSTR(SRCDATA,9,13)) * .0000001) FROM FILENAME

This will select the piece of data you want, and turn it into a decimal
field. Multiplying by .0000001 puts the decimal where you want it.


-----Original Message-----
From: donna lester [mailto:donna_lester_ny@xxxxxxxxx]
Sent: Wednesday, May 14, 2008 1:50 PM
To: RPG programming on the AS400 / iSeries
Subject: sql on flat file

I have a flat file as below :

SRCSEQ SRCDAT SRCDTA
54.00 0 USDAUD 0000009414499 05/13/08
55.00 0 USDEUR 0000015482499 05/13/08
56.00 0 USDGBP 0000019474000 05/13/08

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

I want to extract from this file
USDGBP conversion rate 1.9474000 on date 05/13/08.

Can some one please help me with an SQL or any alternate solution. I donot
have any ideas how can I get period(.) after first digit example :
1.9474000 as flat file comes as 19474000...

Thank you for all the help!





Thanks,

Donna.