Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Unanswered: SQL case statement query

    Hi.

    I have been facing an issue for a few days of messages mismatches.

    We set the Match status using the following query

    set
    MTCH_STA = CASE ((coalesce (CR_AMT,0) +
    :h_CR_AMT) - (coalesce(DB_AMT,0) + :h_DB_AMT))
    WHEN 0 THEN 2 /* matched */
    ELSE 1
    END,


    It seems that DB2 does not treat 0.00 as 0 some of the times as around 1/3rd of the messages remain unmatched even when the CR_AMT , DB_AMT are equal.

    any ideas about this?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What is the data type of the values you claim to be 0.00? If it is a floating point number, then you know that you cannot rely on for exact comparisons anyway.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by maanas
    set
    MTCH_STA = CASE ((coalesce (CR_AMT,0) +
    :h_CR_AMT) - (coalesce(DB_AMT,0) + :h_DB_AMT))
    WHEN 0 THEN 2 /* matched */
    ELSE 1
    END
    Does the following return the same result?
    Code:
    set 
    MTCH_STA = CASE WHEN coalesce(CR_AMT,0) + :h_CR_AMT =
                         coalesce(DB_AMT,0) + :h_DB_AMT
                    THEN 2  ELSE 1    END
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2009
    Posts
    4
    the DB column is DECIMAL(15,3)
    and the host variable is double.

Posting Permissions

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