SQLite if statement in WHERE clause

Question

I know I can use CASE statement in an SQLite query but I don't know how to built it in a WHERE clause.

Actually I have this in a long WHERE clause (this is just the part concerned by the question):

AND (%d >= (wines.year + wines.maturity)) AND (%d < (wines.year + wines.apogee))

In fact I want just that :

AND (%d >= (wines.year + wines.maturity))

=> if wines.apogee IS NULL

or also:

AND (%d >= (wines.year + wines.maturity)) AND (%d < (wines.year + wines.apogee))

=> if wines.apogee IS NOT NULL

How to use the CASE statement in this case to test if wines.apogee IS NOT NULL and add the second part of the request in this case ?

Thanks !

Hm, you can't write `... AND ((wines.apogee is NULL) OR (%d < wines.year + wines.apogee))` in SQLite? — Mr Lister, May 20 '16 at 20:47
Yes, I can write that but this doesn't solve my problem. The CL solution below works. — alex.bour, May 21 '16 at 19:46

score 6 · Accepted Answer · answered May 21 '16 at 06:53

CASE can compute any value, even a boolean value as returned by a comparison. In SQLite, 1 is the same as "true":

...
AND %d >= (wines.year + wines.maturity)
AND CASE WHEN wines.apogee IS NOT NULL
         THEN %d < (wines.year + wines.apogee)
         ELSE 1
    END
...

The same can be done with the ifnull() function:

...
AND %d >= (wines.year + wines.maturity)
AND ifnull(%d < (wines.year + wines.apogee), 1)
...

SQLite if statement in WHERE clause

1 Answers1

Linked