Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Dec 14, 2001

Use 'open' Date in the Oracle SQL Where Clause


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 or
Nvl(: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')) <
nvl(to_char(trunc(EXP_DATE),'YYYYMMDD'),'99999999')

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
… and
nvl(to_char(trunc(FROM_DATE),'YYYYMMDD'),'00000000')) <=
nvl(to_char(trunc(:ar_from),'YYYYMMDD'),'99999999') and
nvl(to_char(trunc(:ar_from),'YYYYMMDD'),'00000000')) <=
nvl(to_char(trunc(TO_DATE),'YYYYMMDD'),'99999999') and
nvl(to_char(trunc(FROM_DATE),'YYYYMMDD'),'00000000')) <=
nvl(to_char(trunc(:ar_to),'YYYYMMDD'),'99999999') and
nvl(to_char(trunc(:ar_to),'YYYYMMDD'),'00000000')) <=
nvl(to_char(trunc(TO_DATE),'YYYYMMDD'),'99999999');
Lev Romanov
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap