'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

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

sqlsql-serveriosapp-store-connect


Solution 1:[1]

You don't need a CASE expression for this, you just need logical operators

SELECT *
FROM customer c
WHERE
(c.cust_id_1 IS NOT NULL AND c.cust_id_1 = @variable)
OR
(c.cust_id_2 IS NOT NULL AND c.cust_id_2 = @variable)
OR
(c.cust_id_3 IS NOT NULL AND c.cust_id_3 = @variable)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dale K

Related Questions