OraFAQ Forum: Reports & Discoverer » How to get all the rows when i pass null values thru the parameter Form. Urgent Plz...

Home » Developer & Programmer » Reports & Discoverer » How to get all the rows when i pass null values thru the parameter Form. Urgent Plz...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get all the rows when i pass null values thru the parameter Form. Urgent Plz... [message #156671]

Thu, 26 January 2006 22:38

srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA

Member

Dear All,

Please help me with this.It is very Very urgent.
I am using Reports 6i.I have a Query Q1 as

select * from emp where sal between :p_losal and :p_hisal

So this is going to create to parameters p_losal and p_hisal.
I am creating a new parameter form with two fields F_losal and F_hisal whose source is p_losal and p_hisal respectively.Now when I run the form it is working perfectly if my user is giving
values for two parameters. When the user is not enetering any values for both the parameters then the report shows no records as my query is getting the null values.

Now my requirement is if both the fields are left null and the user is running the report then he has to get all the records i.e, the Query should work as

select * from emp

.How can I do this? Should I have to use two queries for this and write some code in the after parameter trigger to select one out of two queries depending on the condition.Please explain me with some example in detail.

Thanks in Advance
srini....

Report message to a moderator

Re: How to get all the rows when i pass null values thru the parameter Form. Urgent Plz... [message #156738 is a reply to message #156671]

Fri, 27 January 2006 06:50

srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA

Member

Can anyone answer my Quest plz... It is very urgent.

Report message to a moderator

Re: How to get all the rows when i pass null values thru the parameter Form. Urgent Plz... [message #156740 is a reply to message #156671]

Fri, 27 January 2006 07:04

benoitchabot
Messages: 15
Registered: October 2005
Location: Quebec , Canada

Junior Member

try this:

select *
from emp
where ((:p_losal is not null and :p_hisal is not null and sal between :p_losal and :p_hisal) or
(:p_losal is null or :p_hisal is null))

If it's possible that only one of the 2 parameter have a value
try this:
select *
from emp
where ((:p_losal is not null and :p_hisal is not null and sal between :p_losal and :p_hisal) or
(:p_losal is not null and :p_hisal is null and sal >= :p_losal) or
(:p_losal is null and :p_hisal is not null and sal <= :p_hisal))

Have a nice day
Benoit Chabot

Report message to a moderator

Re: How to get all the rows when i pass null values thru the parameter Form. Urgent Plz... [message #156836 is a reply to message #156740]

Sat, 28 January 2006 06:14

srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA

Member

Thanx a Lot dear benoitchabot. I will try this . I hope this shuld work.

Report message to a moderator

Previous Topic:	REP-51002: Bind to Reports Server <repserv> failed
Next Topic:	what are extended features in forms 6i to 2.5

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 21:03:48 CST 2024