Home » SQL & PL/SQL » SQL & PL/SQL » Parameters and Null dates
Parameters and Null dates [message #196217] Wed, 04 October 2006 09:36 Go to next message
vbattle
Messages: 6
Registered: October 2006
Junior Member
I am working on a select statement that has parameters. If the person leaves the date parameter blank, I want to return both records with null dates and populated dates.
I have tried a couple of methods to try to accomplish this in the where clause:

1) where NVL(TRUNC(i.close_date),TO_DATE('01011900','DDMMYYYY'))
= NVL(:c_closed_date,NVL(TRUNC(i.close_date),TO_DATE('01011900','DDMMYYYY')))

2) where i.close_date = Decode(:c_closed_date, '', i.close_date, :c_closed_date)

Currently, if the parameter is left blank, only records with a populated date are returned. The records with a null date are not returned. I want both sets of records returned. I tried using the plus(+) sign to get both but got an error message. So syntactically I didn't use it right. Please advise.
Re: Parameters and Null dates [message #196220 is a reply to message #196217] Wed, 04 October 2006 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
WHERE ((i.close_date = :close_date)
    OR (:close_date is null))
Re: Parameters and Null dates [message #196476 is a reply to message #196220] Thu, 05 October 2006 08:46 Go to previous messageGo to next message
vbattle
Messages: 6
Registered: October 2006
Junior Member
Thank you so much. That worked. Thanks for the speedy response.
Re: Parameters and Null dates [message #196481 is a reply to message #196476] Thu, 05 October 2006 09:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem with this approach is that it makes it very difficult for the optimiser to produce a decent plan, as it has no idea whether :close_date is null or not at run time.
If possible, I would pick between two queries at run time, one of which specified a Close_Date, and was used when :Close_Date was not null, and one which ignored Close_date altogether, and was used when :close_Date was null.
At least this way the optimiser gets a fighting chance.

Previous Topic: Toms CSV export program
Next Topic: constraint types
Goto Forum:
  


Current Time: Sun Dec 04 22:44:51 CST 2016

Total time taken to generate the page: 0.05137 seconds