Expression to insert value to a table column in SQL

2505 pts.
Tags:
AS/400
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 15  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Perhaps DEC(1/POWER(10,D)) should be more like this: (1/POWER(10,dec(D)))
    51,365 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    FLD1 is defined as (15,2).. OS version is V5R4
    2,505 pointsBadges:
    report
  • philpl1jb
    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.
    51,365 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
    Sorry .. had half my mind on work .. and that's the most of it.
    51,365 pointsBadges:
    report
  • TomLiotta
    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:
    ...SET FLD1 = A*B*C/cast(10**D as int)
    By casting as an INTEGER, the division works much better and final conversion to the FLD1 data type is fairly straightforward.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    …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 
    51,365 pointsBadges:
    report
  • philpl1jb
    so I would probably do a case filling the field with 9s when the result is > 9999999999999.99
    51,365 pointsBadges:
    report
  • philpl1jb
    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. 
    51,365 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
    Apparently not. This was a question about error SQL0406 which is, I believer, an overflow situation.
    51,365 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    ...though it could indeed be replaced by overflow/underflow if the data isn't good. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Great.
    51,365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following