| NULL DATE FIELD [message #409847] |
Wed, 24 June 2009 04:25  |
Nshan
Messages: 62
Registered: February 2009
|
Member |
|
|
Hi All....
My query has to fetch all the records when my parameter is NULL
If i am passing any date then it should select based on that date....
If i am passing 'null' value then it should select field with null value.
ex:
no date
1 null
2 null
3 23-mar-09
i have written a query
select no, date
from xx_tab
where date=nvl(:p_date,date)
so
i.)when i pass :p_date=NULL (No parameter value) then it should display all the 3 rows.
ii.)when i pass 23-mar-09 it is working fine
iii.)when i pass null then it should select 1st & 2nd rows only
when i am trying (i) & (iii) it s not returning any results..
Kindly help on this...
Thanks in advance...
[Updated on: Wed, 24 June 2009 04:32] Report message to a moderator |
|
|
|
| Re: NULL DATE FIELD [message #409857 is a reply to message #409847] |
Wed, 24 June 2009 04:40  |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What exactly is the difference in the parameter value you pass for case 1 and case 3. It appears you pass null in both cases so the behaviour would have to be the same.
And null does not equal null by definition - which is why you're not getting the records where the date is null.
You might be better off using a lexical parameter for this.
|
|
|
|
| Re: NULL DATE FIELD [message #409864 is a reply to message #409857] |
Wed, 24 June 2009 04:52 |
Nshan
Messages: 62
Registered: February 2009
|
Member |
|
|
I am using this query in oracle reports.
While running my report it should ask for three parameters
ie.
case i - if i am not passing any value to that parameter then it should list all the records irrespective of the value present
case iii - if i am passing null value specifically then it should list only records with null value present
ie. 1nd & 2nd row....
when i run my report it will display LOV...
in that LOV
i) if i will not select any value then it has to list all the 3 records...
ii) if i select 'y' then it should list only records with value present ie. date!=null
iii) if i select 'n' then it has to display fields with null value.
ie. date=null;
For more information:
This is my requirement:
List of Values of containing 'Yes' and 'No'. Use standard value set Yes_No.
•If the Inactive parameter is left NULL, then ALL Suppliers will be considered for display in the report.
•If the Inactive parameter is 'Yes', then Suppliers that are inactive will be considered for display in the report, i.e. where AP_SUPPLIERS.END_DATE_ACTIVE is not NULL)
•If the Inactive parameter is 'No', then Suppliers that are active will be considered for display in the report (i.e. where AP_SUPPLIERS.END_DATE_ACTIVE is NULL)
Used to restrict report to display Suppliers that are inactive.
Any idea on this also how to use lexical parameter concept in this??
[Updated on: Wed, 24 June 2009 05:07] Report message to a moderator |
|
|
|
| Re: NULL DATE FIELD [message #409873 is a reply to message #409847] |
Wed, 24 June 2009 05:24 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
First you should realise that if you don't supply a value for a parameter then it's value will be null.
Use a lexical parameter to change the where clause depending on the value from your LOV. Read up on them in report builder help and then use them to apply the logic you've specified at the end of your last message.
|
|
|
|
| Re: NULL DATE FIELD [message #409903 is a reply to message #409873] |
Wed, 24 June 2009 07:10 |
Nshan
Messages: 62
Registered: February 2009
|
Member |
|
|
Since i am using XML data definition not RDF definition any examples on this will be more helpful.
Like In XML data definition how to use lexical parameters and all.
Thanks.
|
|
|
|
| Re: NULL DATE FIELD [message #409914 is a reply to message #409847] |
Wed, 24 June 2009 07:37 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Never used xml in reports myself, maybe someone else has.
Does using xml make any noticable difference to the where clauses you use?
If it doesn't then the fact that you're using xml should make no difference to the lexical parameters
|
|
|
|
| Re: NULL DATE FIELD [message #410962 is a reply to message #409847] |
Wed, 01 July 2009 05:35 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
| Quote: | when i run my report it will display LOV...
in that LOV
i) if i will not select any value then it has to list all the 3 records...
ii) if i select 'y' then it should list only records with value present ie. date!=null
iii) if i select 'n' then it has to display fields with null value.
ie. date=null;
|
Hi
Here is idea for you.
Write code in where clause as follow:
where ((myvalue is null and date=date)
or (myvalue='Y' and date is not null)
or (myvalue='N' and date is null))
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
