SQL case statement query

maanas · #1 (**permalink**) 02-03-09, 17:30

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?

stolze · #2 (**permalink**) 02-03-09, 17:43

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.

Peter.Vanroose · #3 (**permalink**) 02-03-09, 17:47

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

maanas · #4 (**permalink**) 02-03-09, 17:47

the DB column is DECIMAL(15,3)
and the host variable is double.

Internet Services

Web Development

Digital Marketing

Consumer Tech