I have a Spring boot app with custom queries.
I need to find all database rows according to condition - if :myFlag is true, field in database must be in a list (:values here), or null.
Can I use something like this:
select *
from test
where id<4
and (case
when :myFlag then val in (:values)
else val in (:values) or val is null
end)
For example, if I have rows
id | val
1 | 1
2 | null
3 | 3
values = [1,3], myFlag = true, then the query must return rows with id=1,3.myFlag = false, then I need to retrieve rows with id=1,2,3.You don't need a case here,
select ...
from ...
where ...
and (
value in (:values)
OR (:myFlag AND value IS NULL)
)