Thanks for the reply Roger
You were right - NOT pretty - but it definitely works
MUCH appreciated
Thanks
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
"Harman, Roger"
<Roger.Harman@kno
tts.com> To
Sent by: "'Midrange Systems Technical
midrange-l-bounce Discussion'"
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc
10/07/2010 04:39 Subject
PM RE: use sql to update a character
field in one file with a numeric
field from another, right
Please respond to justified, zero fill
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
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
--
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.
midrange.com
