Home » Developer & Programmer » Reports & Discoverer » how to make prametes as optional in reports 6i (reports 6i)
how to make prametes as optional in reports 6i [message #430990] Fri, 13 November 2009 09:57 Go to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
hi all,
i have created my report with 4 parameters like
1.p_empno_from
2.p_empno_to
3.p_deptno_from
4.p_deptno_to

By using this query in datamodal,
select * from emp
where empno between :p_empno_from and :p_empno_to
and deptno between :p_deptno from and :p_deptno_to;

Now i want to make :p_deptno_from and :p_deptno_to as optional.
when i give two parameters of empno then the query will be executed.

how to do this,can any one knows please reply me soon.
thanks in advance.
Re: how to make prametes as optional in reports 6i [message #430991 is a reply to message #430990] Fri, 13 November 2009 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use an OR
select * from emp
where empno between :p_empno_from and :p_empno_to
and (deptno between :p_deptno_from and :p_deptno_to
     OR :p_deptno_from IS NULL
     OR :p_deptno_to IS NULL)
;

Re: how to make prametes as optional in reports 6i [message #430992 is a reply to message #430990] Fri, 13 November 2009 10:10 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
if i were you ,i will define one variable of type char and make it as 1=1 as a default value....and write the Query like this
 select * from emp 
where ...your condition and &that  variable.
.If you omit passing the value for that it will added to the query as "and 1=1 so your query should work....


Hope you understood..... Smile (but if you again want that condition to be included, pass the condition as char.

sriram Smile
Re: how to make prametes as optional in reports 6i [message #430995 is a reply to message #430991] Fri, 13 November 2009 10:16 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
thank you for your reply,
i done it.

But i want to make the parameters :p_deptno_from and :p_deptno_to
as optional in property palette by using 'validation trigger'.

if you know then reply me.
thank you.
Re: how to make prametes as optional in reports 6i [message #431000 is a reply to message #430995] Fri, 13 November 2009 10:40 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
You can implement that using the same logic..if both met the condition the return the hole condition into the variable i suggested...else return the condition as 1=1 Smile

If you want another way..say some reason why? As you posted "If we know then only reply"...

Now you say why? we? Wink
Re: how to make prametes as optional in reports 6i [message #431010 is a reply to message #430990] Fri, 13 November 2009 11:20 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
i want to do this functionality because, i want to learn the scenarios by practically,how can we expect only one solution which is known for us is suitable for all the requirements.

and the thing is that 'if you know then reply me' is i recieved the solution for my first requirement, and again i am giving another requirement,if you know solution for this also then reply me,thats why i posted it.

Re: how to make prametes as optional in reports 6i [message #431013 is a reply to message #430995] Fri, 13 November 2009 11:35 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
sivaora wrote on Fri, 13 November 2009 16:16
thank you for your reply,
i done it.

But i want to make the parameters :p_deptno_from and :p_deptno_to
as optional in property palette by using 'validation trigger'.

if you know then reply me.
thank you.


Unless I'm missing something here parameters are optional by default. So why do validation triggers have anything to do with it?
Re: how to make prametes as optional in reports 6i [message #431023 is a reply to message #430990] Fri, 13 November 2009 12:31 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
parameters are not optional by default.
if you are not entering any value in to the parameters then null values will by substituted in query.

in my above example if i am not making the parmaetes for deptno as optional and i am not entering any values then null will be substitued in query and we are not getting any output.

that's why we have to make the parameters as optional.
Re: how to make prametes as optional in reports 6i [message #431077 is a reply to message #431023] Sat, 14 November 2009 08:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you might consider using a lexical parameter here.
Re: how to make prametes as optional in reports 6i [message #431255 is a reply to message #431023] Mon, 16 November 2009 05:59 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
sivaora wrote on Fri, 13 November 2009 18:31
parameters are not optional by default.
if you are not entering any value in to the parameters then null values will by substituted in query.

in my above example if i am not making the parmaetes for deptno as optional and i am not entering any values then null will be substitued in query and we are not getting any output.

that's why we have to make the parameters as optional.


We've got different definitions of optional here. When I say optional I mean don't specify a value so null gets passed.
Assuming you can't have null deptno's in your table then passing null is fine and the query I supplied above will work.
Otherwise you need to use a lexical parameter as suggested by Littlefoot.
Previous Topic: Particular column sum values to be hide
Next Topic: Page skip not working in 10g character mode report
Goto Forum:
  


Current Time: Thu Mar 28 21:27:27 CDT 2024