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 )