DB2 SQL Statement WHERE clause CASE WHEN in multiple conditions

Question

This is what I am trying to do:

SELECT 
    id, name 
FROM 
    users 
WHERE 
    isActive=true
    (AND CASE WHEN {param} != null THEN name={param} ELSE null END)

if the passed {param} is not null then only the AND operator will be added otherwise just isActive=true condition will be used.

Answer 1

You can use something like COALESCE or (in case of DB2) NVL.

SELECT 
    id, name 
FROM 
    users 
WHERE 
    isActive=true
    AND name=COALESCE({param},name)

You didn't say how you pass parameters/variables, so i'll leave it to you. "{param}" could be replaced with a column or constant in this example

Answer 2

try this

SELECT 
    id, name 
FROM 
    users 
WHERE 
    isActive=true
 AND (({param} is not null AND name={param}) OR ({param} is null))