× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.