Thanks for your reply Steve
This one also 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
"Needles,Stephen
J"
<SNEEDLES@travele To
rs.com> "Midrange Systems Technical
Sent by: Discussion"
midrange-l-bounce <midrange-l@xxxxxxxxxxxx>
s@xxxxxxxxxxxx cc
Subject
10/07/2010 04:50 RE: use sql to update a character
PM field in one file with a
numeric field from another, right
justified, zero fill
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
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
--
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 communication, including attachments, is confidential, may be subject
to legal privileges, and is intended for the sole use of the addressee. Any
use, duplication, disclosure or dissemination of this communication, other
than by the addressee, is prohibited. If you have received this
communication in error, please notify the sender immediately and delete or
destroy this communication and all copies.
--
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
