You need to convert the decimal part into a character representation.
Depending on what you need you may try one of the following solutions.
update masterf
set DESCR1 = left(descr1, 1) concat '' concat substr(descr1, 5, 21) concat
'' concat Varchar(dec( "VALUE" * 100 , 4, 0))
Or
update masterf
set DESCR1 = left(descr1, 1) concat '' concat substr(descr1, 5, 21) concat
'' concat Digits(dec( "VALUE" * 100 , 4, 0))
VarChar converts the numeric value into a left adjusted character
representation without leading zeros but with decimal separator and sign.
Digits converts a numeric value into a character representation with leading
zeros and without decimal separators and sign.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von A Paul
Gesendet: Wednesday, 20.2 2013 01:10
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: SQL
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
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
