How can I use a CASE statement in a WHERE clause with IS NULL?

Question

Here's my queries, they don't work but I want to do something like this :

SELECT a_field FROM a_table
WHERE
... 
AND
CASE
WHEN a_value_from_another_query IS NULL THEN a_second_field IS NULL
ELSE a_second_field = a_value_from_another_query
END

Or

SELECT a_field FROM a_table
WHERE
... 
AND
CASE a_value_from_another_query
WHEN NULL THEN a_second_field IS NULL
ELSE a_second_field = a_value_from_another_query
END

Or

SELECT a_field FROM a_table
WHERE
... 
AND
CASE NVL(a_value_from_another_query, 'x')
WHEN 'x' THEN a_second_field IS NULL
ELSE a_second_field = a_value_from_another_query
END

When a_value_from_another_query IS NULL, I want to add a_second_field IS NULL to my WHERE clause, when a_value_from_another_query IS NOT NULL, I want to add a_second_field = a_value_from_another_query to my WHERE clause. How can I achieve this ?

Answer 1

Sounds like you simply picked up the wrong tool from the toolbox.

Unless I have horribly misunderstood you, the following:

WHERE
    (a_value_from_another_query IS NULL AND a_second_field IS NULL)
  OR
    (a_value_from_another_query IS NOT NULL AND a_second_field = a_value_from_another_query)

... should so what you want.

Answer 2

There are two ways to use a CASE statement:

 1. CASE WHEN condition_1 THEN return_expr_1 
    [WHEN condition_2 THEN return_expr_2 ….] 
    [WHEN condition_n THEN return_expr_n ….] 
     [ELSE default] END 
 2. CASE expression WHEN value1 THEN result1
[WHEN value2 THEN result2
.....
ELSE resultn
]
END

In your selects, you are using instead a result, another expression. This isn't going to work. If you want to get your query working, you have to use the first case expression, and return a value, something like this:

SELECT a_field FROM a_table
WHERE
... 
AND nvl(a_second_field,'x')=(CASE WHEN a_value_from_another_query IS NULL THEN 'X' 
ELSE a_value_from_another_query END)