Re: SQL -- RPG400-L

Thanks Lim and Chuck for your answers. I try using below

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

I get error
Conversion error on assignment to column DESCR1. I checked the length to see it does not exceed 30 char allowed on it, but still ending up with the error. I tried to change the 21 to 20/19/18, it didnt work

  I get descr1 correctly fits 30 char in below select sql and used same above in update
select left(descr1, 1) concat '' concat
substr(descr1, 5, 21) concat '' concat
dec( "VALUE" * 100 , 4 ) from masterf

It appeared to me it could be something wrong with the part of dec("VALUE" *100, 4) ?

Thanks for your help!

Message . . . . :   Conversion error on assignment to column DESCR1.
Cause . . . . . :   During an attempt to assign a value to column DESCR1 with
   an INSERT, UPDATE, ALTER TABLE, or REFRESH TABLE statement, conversion error
   type 1 occurred. If precompiling, the error occurred when converting a
   numeric constant to the same attributes as column DESCR1. A list of the
   error types follows:
     -- Error type 1 is overflow.
     -- Error type 2 is floating point overflow.
     -- Error type 3 is floating point underflow.
     -- Error type 4 is a floating point conversion error.
     -- Error type 5 is not an exact result.
     -- Error type 6 is numeric data that is not valid.
     -- Error type 7 is DBCS data that is not valid.
From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Wednesday, 13 February 2013, 23:19
Subject: Re: SQL

On 10 Feb 2013 15:13, A Paul wrote:

Value will contain one digit or max two digits on the left side of
the decimal point. So it could be 1.2500000000 or 12.500000000, all
positive values only.

So the remaining question for varied numeric values of the "VALUE"
column was about the relevance of alignment in the outcome. If no
matter, then the given statement should suffice; i.e. values greater
than or equal to one but less than ten will align differently across
rows than either those values from zero to less than one, and those
values from ten to less than one hundred.

If alignment is important, and leading zeroes are acceptable, then
use of the DIGITS() as shown by the response from Lim Hock-Chai should
suffice.

If both alignment and no leading zeroes are required, then the either
a more complex expression is required for the concatenation of the
"VALUE" data to the string, or that complexity is best deferred to a UDF
to perform the right-alignment of the numeric being cast to character
[because the SQL provides no builtin scalar to do that :-( ].

Example rows with the smaller positive values:

DESCR1 VALUE
....+....1....+....2....+....3
XXX 01FEB 13 AXA INS LHRTD 12.50000000
XXX 01MAR 13 AXA INS LHRTD 1.25000000
XXYY 04MAR 13 AXA INS LHRTD 0.12500000

Effect using the expression I offered, the alignment is not fixed:

DESCR1 VALUE
....+....1....+....2....+....3
X 01FEB 13 AXA INS LHR 1250 12.50000000
X 01MAR 13 AXA INS LHR 125 1.25000000
X 04MAR 13 AXA INS LHR 12 .12500000

Effect using the expression using DIGITS, the alignment is fixed, but
the numbers have leading zeroes:

DESCR1 VALUE
....+....1....+....2....+....3
X 01FEB 13 AXA INS LHR 1250 12.50000000
X 01MAR 13 AXA INS LHR 0125 1.25000000
X 04MAR 13 AXA INS LHR 0012 .12500000

Or perhaps neither of those is satisfactory, and the following is the
desired effect?:

DESCR1 VALUE
....+....1....+....2....+....3
X 01FEB 13 AXA INS LHR 1250 12.50000000
X 01MAR 13 AXA INS LHR 125 1.25000000
X 04MAR 13 AXA INS LHR 12 .12500000