sql - oracle compare date to null -
i have query comparing date 1 table max date table. problem having when date null being compared against. take following query:
select col1 table1 date > (select max(date) table b);
the query works fine except when subquery returns 0 rows. in case of 0 rows want return rows. in other words want date compare datetime min value. in case of 0 rows want compare:
date > '01-01-1900'
i have tried using case statements no luck feel missing simple.
any appreciated
you want use nvl function assist.
(nvl(p_datefield, to_date('01/01/1901','mm/dd/yyyy'))
so you:
select col1 table1 date > (select max(nvl(date, to_date('01/01/1901','mm/dd/yyyy')) table b);
the idea 01/01/1901 represents null
. if don't it, change date can come grips on represent null
on behalf.
update #1..a comment below suggests coalesce work better. could...so here's example:
select col1 table1 date > (select coalesce(date, to_date('01/01/1901','mm/dd/yyyy')) table b);
now have choice. enjoy.
Comments
Post a Comment