| Parameters and Null dates [message #196217] |
Wed, 04 October 2006 09:36  |
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 #196481 is a reply to message #196476] |
Thu, 05 October 2006 09:35  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
