Ashwin Balachandar Ashwin Balachandar - 4 months ago 35
SQL Question

Oracle Sysdate > NULL

I am looking into a scenario some similar to future dated stuff.

I have a table something similar to this

ProductID ProductStatus EffectiveFromDate EffectiveToDate CancelledIndicator
----------- ------------- ----------------- --------------- ------------------
345 A 7/7/2016 (null) 1
345 S 7/7/2016 11/7/2016 (null)
345 A 12/7/2016 (null) (null)


I need to fetch the current dated product
if I find a cancelled indicator it means it is no more active
if their are two rows one with future dated status.
Based on the table above I get a latest record if I check for the efd < sysdate and etd is null. but to get the current active status which is the case which i need to implement.

I need to check if the sysdate is b/w the efd and etd of the older record if not I need to take the latest record which will be the current status.

I have query which does that

but the thing is what happens when I check

sysdate between efd and etd where etd can be null most of the time.

Answer

Some samples of how you can handle it:

setup:

SQL> create table testNull (id, startDate, endDate) as
  2  (
  3  select 1, null,       sysdate + 1 from dual union all
  4  select 2, sysdate -1, sysdate + 1 from dual union all
  5  select 3, sysdate -1, null        from dual union all
  6  select 4, sysdate -3, sysdate - 1 from dual
  7  );

Table created.

without handling NULL:

SQL> select *
  2  from testNull
  3  where sysdate between startDate and endDate ;

        ID STARTDATE ENDDATE
---------- --------- ---------
         2 11-LUG-16 13-LUG-16

with COALESCE:

SQL> select *
  2  from testNull
  3  where sysdate between startDate and coalesce(endDate, sysdate);

        ID STARTDATE ENDDATE
---------- --------- ---------
         2 11-LUG-16 13-LUG-16
         3 11-LUG-16

with some boolean logic:

SQL> select *
  2  from testNull
  3  where sysdate >= startDate
  4    and ( endDate is null or sysdate <= endDate);

        ID STARTDATE ENDDATE
---------- --------- ---------
         2 11-LUG-16 13-LUG-16
         3 11-LUG-16