Is there an SQL function that let us put condition above WHERE clause?

I have some query with several WHERE clauses. Based on an external variable :flag, I would like to set a condition when I can use a WHERE clause. Below is a presentation of what I mean. There is a pseudo if else statement which I’d like to have.

    SELECT * FROM table
    WHERE cond1 = sth1
    AND cond2 = sth2
    --if :flag == 1 then (AND cond3 = sth3) else pass
    AND cond4 = sth4

Answer

You can use regular boolean logic for this:

WHERE cond1 = sth1 AND
      cond2 = sth2 AND
      cond4 = sth4 AND
      (:flag <> 1 OR cond3 = sth3)

This form assumes that :flag is not NULL. It can be tweaked to handle that situation.

Advertisement

Answer