COALESCE in SQL problems?

Vote count:

0

I'm trying to use COALESCE in WHERE clause but may be is not the good keyword. If a field is equal to a specific value, I want this record if not I want the record with this field equal to null.

SELECT TOP 1 COL1, COL2, COL3
FROM TABLE1 WHERE COL2 = COALESCE(MY_SPECIFIC_VALUE, COL2) AND COL3 = '42'
AND COL1 = 3

Why I have no result? Because, I have a record with COL2 is null.

SELECT TOP 1 COL1, COL2, COL3 FROM TABLE1 WHERE COL2 = '42' AND COL1 = 3

COL1 COL2 COL3 
 3    NULL  42

I can try to that :

SELECT TOP 1 COL1, COL2, COL3
FROM TABLE1 WHERE (COL2 = MY_SPECIFIC_VALUE OR COL2 IS NULL) AND COL3 = '42'
AND COL1 = 3

But, what is the result returned? The smallest id? Or it is not specified?

Thx,

asked 2 mins ago

Nuz

13