Using SQL AND only when the value is not null

Question

I am using Postgres. A filter query something like this:

SELECT * FROM Entity E
WHERE E.field1 = ?
AND E.field2 = ?
AND E.field3 = ?
AND E.field4 = IN (?field4[])

?field1 and ?field2 are mandatory fields so they will be present always. ?field3 and ?field4 are optional. I would like to use AND E.field3 = ? & AND E.field4 = IN (?field4[]) only when the value of ?field3 and ?field4[] is present, and ignore otherwise

when field3 adn 4 doesnt have any value pls pass some value like 'default_val' and then try this - SELECT * FROM Entity E WHERE E.field1 = ? AND E.field2 = ? AND (E.field3 = ? OR E.field3 = 'default_val') AND (E.field4 = IN (?field4[]) OR E.field4 = 'default_val') — Koushik Roy
– Koushik Roy, Commented Nov 9, 2020 at 4:54
You can use JpaSpecificationExecutor repository method findAll(Specification<T> spec, Pageable pageable). This solution allows you to extend the parameters list using the same repository and service API. I have answered a similar question here stackoverflow.com/questions/58240024/… — Oleksii Valuiskyi
– Oleksii Valuiskyi, Commented Nov 9, 2020 at 7:12

Akhilesh Mishra · Accepted Answer · 2020-11-09 05:19:43Z

1

You can use case statement in where clause like below

SELECT * FROM Entity E
WHERE 
CASE WHEN E.field3 IS NOT NULL and E.field4 IS NOT NULL THEN
E.field1 = ?
AND E.field2 = ?
AND E.field3 = ?
AND E.field4 IN (?field4[])
ELSE
E.field1 = ?
AND E.field2 = ?
END

answered Nov 9, 2020 at 5:19

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rsp Over a year ago

NULL, NOT NULL these checks I have to apply on the parameters that I am passing, not on the existing column values

Akhilesh Mishra Over a year ago

You want to check the value of parameter or column? if you want to compare it with values you are passing, then simply replace E.field3 with ?field3 and E.field4 with ?field4 in case when

Collectives™ on Stack Overflow

Using SQL AND only when the value is not null

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related