The most common example where ‘open’ date is used is in the expiration_date for an entry. It could be set for a particular date, or be NULL if the expiration date isn’t defined (open date). It is also possible to ‘open’ date use in the where clause:

 EXP_DATE is NULL or(EXP_DATE is NOT NULL and EXP_DATE < :ar_date)

Assume that argument :ar_date could be NULL as well. Oracle doesn't allow you to use 'is Null' for arguments, but function nvl() could be used instead:

 EXP_DATE is NULL orNvl(:ar_date,' ') = ' ' or(EXP_DATE is NOT NULL and   Nvl(:ar_date,' ') <> ' ' and   EXP_DATE < :ar_date)

All the above could be combined in the single comparison expression:

 nvl(to_char(trunc(:ar_date),'YYYYMMDD'),'00000000')) 

First, the date's comparison is substituted by the comparison of strings. If there are any null values, in both cases they will be substituted by the minimum or maximum numbers--depending what side of comparison they are located. This is how the open date is treated. Second, the comparison operator could be '<' or '<=', which doesn't limit the technique. Plus, this method makes SQL more readable as can be seen in the example below (rewrite the example using another technique if you are not sure about this).

Usage example: If there is a need to select entries that were active in a particular date range (from :ar_from to :ar_to) and all table's values and arguments could be null, SELECT will be:


 SELECT …FROM …WHERE… andnvl(to_char(trunc(FROM_DATE),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(:ar_from),'YYYYMMDD'),'99999999') andnvl(to_char(trunc(:ar_from),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(TO_DATE),'YYYYMMDD'),'99999999') andnvl(to_char(trunc(FROM_DATE),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(:ar_to),'YYYYMMDD'),'99999999') andnvl(to_char(trunc(:ar_to),'YYYYMMDD'),'00000000')) <=nvl(to_char(trunc(TO_DATE),'YYYYMMDD'),'99999999');