Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Question Unanswered: Unix DB2 SQL rounding issue

    Within a query I have the following SQL;
    FAP.PLCY_CNTRCT_NUM,
    BIGINT ( (DAYS (
    CASE WHEN FASC.END_EFF_DT <= '01/31/2012'
    THEN FASC.END_EFF_DT
    ELSE
    '01/31/2012'
    END)
    - DAYS (
    CASE
    WHEN FASC.EFF_DT >= '01/01/2012' THEN FASC.EFF_DT
    ELSE '01/01/2012'
    END)
    + 1))
    * CAST (
    (FASC.NEW_FULL_TERM_PREM_AMT
    / INTEGER (
    CASE
    WHEN FAP.FULL_TERM_DAYS_NUM = '0'
    THEN
    CASE
    WHEN FAP.TERM_LNGTH_MONTHS_NUM = 6 THEN '180'
    ELSE '360'
    END
    ELSE
    FAP.FULL_TERM_DAYS_NUM
    END)) AS DECIMAL (10, 2))
    AS PREM

    NEW_FULL_TERM_PREM_AMT is DECIMAL(9,2) with a value of 6.10
    FULL_TERM_DAYS_NUM is VARCHAR(20) with a value of '183'
    FULL_TERM_DAYS_NUM is SMALLINT with a value of 6


    in the actual values, the first CASE evaluates to '01/31/2012', the second CASE to '01/01/2012', so the actual equation is 31*6.10/183 = 1.03. The query is returning a value of 0.93 because it appears to be truncating the
    division result to DECIMAL(9,2) before multiplying. How do I fix?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you try to CAST the columns as (11,4) or something like that?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    FULL_TERM_DAYS_NUM is VARCHAR(20) with a value of '183'
    FULL_TERM_DAYS_NUM is SMALLINT with a value of 6
    i doubt that db2 can figure this out any better than i can
    Dick Brenholtz, Ami in Deutschland

Tags for this Thread

Posting Permissions

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