Re: SQL

Hi Chuck
Thanks a lot, very appreciated.
Value will contain one digit or max two digitss on the left side of the decimal point. So it could b 1.2500000000 or 12.500000000, all positive values only.
 
Thanks,
Amanda.

From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Sunday, 10 February 2013, 22:55
Subject: Re: SQL

On 10 Feb 2013 12:58, A Paul wrote:

My pf(masterf) has descr1 (30 char), value(15 8) field
I would like to change description  DESCR1 as shown below:

DESCR1                          LOC    BRANCH          VALUE

+ ....+....1....+....2....+....3

ABC  01FEB 13 AXA INS LHRTD      91    LON            45.00000000
ABC  01MAR 13 AXA INS LHRTD      91    LON            41.00000000
ABC  04MAR 13 AXA INS LHRTD      91    LON            42.00000000
BALL 05MAR 13 AXA INS LHRTD      91    LON            43.00000000
BALL 06MAR 13 AXA INS LHRTD      91    LON            44.00000000

====================changed as below===========================

DESCR1                          LOC    BRANCH          VALUE

+ ....+....1....+....2....+....3

A  01FEB 13 AXA INS LHR  4500    91    LON            45.00000000
A  01MAR 13 AXA INS LHR  4100    91    LON            41.00000000
A  04MAR 13 AXA INS LHR  4200    91    LON            42.00000000
B  05MAR 13 AXA INS LHR  4300    91    LON            43.00000000
B  06MAR 13 AXA INS LHR  4400    91    LON            44.00000000

<<SNIP>>

  Note: There is nothing about the inquiry which is specific to the RPG.

  There is a conspicuous potential problem with the VALUE column
definition and the described as-expected results.  Are all values only
two digits of precision irrespective of the much larger definition?  If
not, must the data right-align as with typical numeric or is
left-alignment acceptable?  Whether the spaces preceding the multiplied
numeric that was cast to character are part of the number in the updated
text is not clear.  Certainly the limit of 30 characters becomes an
issue if there are not very restrictive limits [two max] on the numeric
values.  What is the expectation for a value of 1.25, or 0.25, or
321.25?  What is the expectation for any of those same values as a
negative instead of positive?

  Assuming the four tokens in the middle of DESCR1 are consistent, or
at least that the start position 5 and end positions 25 are correct as
unchanged data consistently across all rows, and all of VALUE are only
two digits or one digit of precision and left-adjust is acceptable, then
the following may give the desired effect:

    update masterf
      set DESCR1 = left(descr1, 1) concat ' ' concat
            substr(descr1, 5, 21) concat '  ' concat
            dec( "VALUE" * 100 , 4 )