I need the WHERE clause to change what column it is evaluating when NULL is encountered. For instance I'm trying to do something like this: Is something like this possible? One of the 3 cust_id's will not be NULL. Answer That seems like less than optimal table design, but isn't a simple COALESCE where you're after?

How to handle NULL values in WHERE clause and change target column based upon its encounter

I need the WHERE clause to change what column it is evaluating when NULL is encountered. For instance I’m trying to do something like this:

SELECT *
FROM customer c
WHERE CASE WHEN c.cust_id_1(@variable) IS NOT NULL THEN c.cust_id_1 = @variable
         ELSE CASE WHEN c.cust_id_2(@variable) IS NOT NULL THEN c.cust_id_2 = @variable
              ELSE c.cust_id_3 = @variable

​x
 
SELECT *FROM customer cWHERE CASE WHEN c.cust_id_1(@variable) IS NOT NULL THEN c.cust_id_1 = @variable         ELSE CASE WHEN c.cust_id_2(@variable) IS NOT NULL THEN c.cust_id_2 = @variable              ELSE c.cust_id_3 = @variable​

Is something like this possible? One of the 3 cust_id’s will not be NULL.

Answer

That seems like less than optimal table design, but isn’t a simple COALESCE where you’re after?

WHERE @variable = COALESCE(cust_id_1, cust_id_2, cust_id_3);

 
WHERE @variable = COALESCE(cust_id_1, cust_id_2, cust_id_3);​

Advertisement

Answer