Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2017
    Posts
    9

    Post Unanswered: DB2 SQL Division issue - Numeric value out of range.

    Error during SQLFetch: 22003(-99999)[IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003 (0.11 secs)

    SELECT CLIENT_KEY,
    DEALER_CODE,
    (COALESCE(TOTAL_SETTLEMENT_RISK_LIMIT,0) / (Select input_value from ABC.TRANSFORMATIONS
    where PROJECT_NAME = '2100' and TRANSFORM_NAME='VARRATE')) AS AVAILABLE_PRINCIPAL
    FROM ABC.CLIENT_DTL
    WHERE TOTAL_SETTLEMENT_RISK_LIMIT is null
    TOTAL_SETTLEMENT_RISK_LIMIT, can fetch both null and not null values. The sql fails with above error message on not null scenario but it goes well on null scenario. Also the sql goes well when the denominator of color shaded statement that is
    Select input_value from ABC.TRANSFORMATIONS where PROJECT_NAME = '2100'
    and TRANSFORM_NAME='VARRATE'
    is substituted with it's actual value (0.0433) regardless of the RISK_LIMIT is null.

    Any help would be appreciated. Thanks in advance.
    Last edited by srinivas_sp; 08-18-17 at 14:19.

  2. #2
    Join Date
    Aug 2017
    Posts
    9

    resolved

    The numeric value out of the range error has been resolved by adding decimal precision to input_value as below.

    Select dec(input_value,5,4) from ABC.TRANSFORMATIONS where PROJECT_NAME = '2100'
    and TRANSFORM_NAME='VARRATE'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •