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
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.