CODEWITHSUNDEEP
sqldatabaseoracle-database
Maxqueue
Maxqueue

Reputation: 2444

oracle compare date to null

I have a query where i am comparing a date from one table to a max date from another table. The problem i am having is when the date is null that is being compared against. So take the following query:

select col1 from table1 where date > (select max(date) from table b);

The query works fine except for when the subquery returns 0 rows. In the case of 0 rows i want it to return all rows. So in other words i want the date to compare to a datetime min value. So for in the case of 0 rows i want to compare:

date > '01-01-1900'

I have tried using case statements with no luck but i feel like i am missing something simple.

Any help would be much appreciated

Upvotes: 1

Views: 26894

Answers (3)

Michel Riondel
Michel Riondel

Reputation: 31

Actually it would be like this:

select NVL(max(date, to_date('01/01/1901','MM/DD/YYYY')) from table b

Since: you want the max value from the table and then check on null value – not the other way around.

Upvotes: 0

Momus
Momus

Reputation: 394

What do you want to happen when the subquery returns no rows? A comparison of a date against null will result in nothing being returned by the main query - which is technically the correct response. If you don't want that then you would need to substitute a string value for the null using the NVL function.

Upvotes: 0

arcee123
arcee123

Reputation: 211

you want to use a NVL function to assist. 

(NVL(p_datefield, to_date('01/01/1901','MM/DD/YYYY'))

so for you:

select col1 from table1 where date > (select max(NVL(date, to_date('01/01/1901','MM/DD/YYYY')) from table b);

the idea is 01/01/1901 represents null. If you don't like it, then change that date to something you can come to grips on to represent null on your behalf.

UPDATE #1..a comment from below suggests that COALESCE would work better. It could...so here's that example:

select col1 from table1 where date > (select coalesce(date, to_date('01/01/1901','MM/DD/YYYY')) from table b);

Now you have a choice. Enjoy.

Upvotes: 4

Related Questions