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

Question

Here are my requests, they do not 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

It looks like you just took the wrong tool from the toolbar.

If I did not understand you correctly, 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 the 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 options, you use the result, a different expression instead. This will not work. If you want your query to work, you should 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)