CODEWITHSUNDEEP
sqloracle-databasespring-data-jpa
Ankur Goel
Ankur Goel

Reputation: 97

don't append "and" clause in where condition when column value is null

I have a query, in which i don't want to append "and" clause in where condition when column value is null e.g 

select * from x where x.abc = 'ACTIVE' and x.start_date < sysdate and x.end_date > sysdate

So in this i want x.end_date will apply only if its value is not null.Not having any stored procedure, just a simple query.

I also want to convert this query into spring-data jpa specifications using criteria builder and predicates.

Upvotes: 3

Views: 391

Answers (3)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Not that the other answers aren't right or good, but I would write this a little differently and I post it here in the hopes that someone has an interesting comment about why one way might be better than another.

Here's what I'd write:

select * from x
 where x.abc = 'ACTIVE' 
   and sysdate between x.start_date and nvl(x.end_date,sysdate);

To me, that reads: "the current date must fall within the active date range of the record".

Upvotes: 0

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

You can have a look at the NVL function for this. 

select * from x
 where x.abc = 'ACTIVE' 
   and x.start_date < sysdate 
   and NVL(x.end_date,sysdate) >= sysdate

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use or:

select *
from x
where x.abc = 'ACTIVE' and
      x.start_date < sysdate and
      ( x.end_date > sysdate or x.end_date is null );

Upvotes: 5

Related Questions