RE: use sql to update a character field in one file with a numeric field from another, right justified, zero fill

Roger's is more elegant and simpler.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
Sent: Thursday, October 07, 2010 3:38 PM
To: Midrange Systems Technical Discussion
Subject: RE: use sql to update a character field in one file with a numeric field from another, right justified, zero fill

Use something like:

Set field2 = substring('000000000',1,10-length(trim(char(field1)))) ||
trim(char(field1))

explanation:

if field1 = 12.23, you will need 5 leading zeros.

length(trim(char(field1))) = 5 and (10 - 5) = 5

This should take the number of required leading zeroes and put them in the front...

'00000'

then the trim(char(field1)) = 12.23, which will be cat'ed to the '00000' resulting in:

'0000012.23'


steve

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, October 07, 2010 2:56 PM
To: midrange-l@xxxxxxxxxxxx
Subject: use sql to update a character field in one file with a numeric field from another, right justified, zero fill


Hi everyone
hopefully the subject line makes sense
I have 2 files
I need to update a 10 character field in one file with a numeric field (6,
2), right justify, with decimal point, zero fill

for example
field1 (6, 2) field2 (10A)
12.23 would become 0000012.23
175.00 would become 0000175.00
1234.56 would become 0001234.56

I tried the following SQL
update file2 a set a.field2 =
(select b.field1
from file1 b
where
a.fieldA = b.fieldA and
a.fieldB = b.fieldB)
where exists
(select b.field1
from file1 b
where
a.fieldA = b.fieldA and
a.fieldB = b.fieldB))

This resulted in the following
12.23
175.00
1234.56
left justified with trailing blanks

I also tried using digits(b.field1) but this resulted in no decimal point

Hopefully this makes sense
As always all responses gratefully accepted

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