Expression to insert value to a table column in SQL
My CL program has RUNSQLSTM. The SQL source has some SQL statements(source is of type SQL) which inserts value to a file. I need to change the SQL to have a column(FLD1) value like this
(A*B*C)/10**D. A,B,C, and D are file fields. The attributes are like this
A- (15,2)
B- 13
C - (13,5)
D - 1
I have put the expresstion as (A*B*C) *DEC(1/POWER(10,D)) FLD1
I am getting error SQL0406 Position 1 Conversion error on assignment to column
FLD1.
Please let me know how to put the expression.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 17, 2013 10:11 AM
UPDATED:
January 17, 2013 10:19 AM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
Perhaps DEC(1/POWER(10,D)) should be more like this:
(1/POWER(10,dec(D)))
A- (15,2) B- 13 C – (13,5)
(1234567890123.45 * 1234567890123 * 12345678.90123)
In theory, that gives an intermediate result defined as 34 digits to the left and 7 digits to the right of the decimal point. What OS version are you running? What is the definition of column FLD1?
Tom
FLD1 is defined as (15,2).. OS version is V5R4
RPGLE is rather particular about stuffing a 41.7 digit number into a field that is 15,2 even if there are a lot of zeros in the number.
No RPG involved in this, AFAIK. But multiplying all those large fields together even in SQL could be an issue. I might get a chance to create a test file this evening and see what happens. — Tom
Sorry .. had half my mind on work .. and that’s the most of it.
I should have thought of this immediately. The exponentiation is causing an intermediate floating-point result to be created, and that causes floating-point to be carried through each subsequent intermediate result. Try it this way:
By casting as an INTEGER, the division works much better and final conversion to the FLD1 data type is fairly straightforward.
Tom
…SET FLD1 = A*B*C/cast(10**D as int)Will fail with divide by zero error when D is a negative number…Doesn’t matter what you do on the right, process will fail with error SQL0406 when updating FLD1 when the answer has more than 13 significant digits as in 1234567890123.45 * 1234567890123. * 12345678.90123 / 10 * 1
so I would probably do a case filling the field with 9s when the result is > 9999999999999.99
Don’t know if a case can be used in the update..might be two updates with where clauses .. 99999etc where A*B*C/10**D > 9999999999999.99 and the other updates the field with the actual value.
Error handling for possible overflow/underflow is always a good idea. Can’t argue against it. However, the values in A, B, C and D should sanity checked at the point of entry (or update). If values that will be trouble are not allowed into the file in the first place, later tests can be minimized for processing efficiency. This current process is only updating FLD1, so it might be rational to _assume_ that the existing values will work. Maybe. — Tom
Apparently not. This was a question about error SQL0406 which is, I believer, an overflow situation.
SQL0406 is “Conversion error on assignment to column &2.” It’s a conversion error (as noted in the question). It goes away when the CAST() is done. — Tom
…though it could indeed be replaced by overflow/underflow if the data isn’t good. — Tom
Great.