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

Try this for your b.field1. Not pretty but it will work.

right(trim(concat('0000000000',trim(char(b.field1)))),10)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, October 07, 2010 12: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