Home » Developer & Programmer » Reports & Discoverer » NULL DATE FIELD
NULL DATE FIELD [message #409847] Wed, 24 June 2009 04:25 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
dude4084
Messages: 221
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))


Re: NULL DATE FIELD [message #415477 is a reply to message #410962] Tue, 28 July 2009 01:17 Go to previous message
Nshan
Messages: 62
Registered: February 2009
Member
Thanks for all your replies.
It s done using lexical parameter
Previous Topic: How to convert a report from American Letter format (216mmX279mm) to DIN A4 format (210mmX297mm)
Next Topic: Invoice information in MTL_MOVEMENT_STATISTICS
Goto Forum:
  


Current Time: Sat Dec 03 22:22:55 CST 2016

Total time taken to generate the page: 0.07559 seconds