Home » Developer & Programmer » Reports & Discoverer » parameter with value 'ALL'
parameter with value 'ALL' [message #388074] Mon, 23 February 2009 02:11 Go to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Hi

i have a parameter in my report set to list of values

select dp_name from dept;

dp_name is - IT,Bussiness.........etc
when the user select a department the specific records are displayed.

Now i want to add 'ALL' to my parameter LOV so that if the user selects 'ALL' ,all the departments will be displayed.

I tried :
LOV list of values as :
select null from dual
union
select dp_name from dept;

then my query was:

SELECT .....FROM ...where

INV_DEPT.DP_NAME = NVL(:p,INV_DEPT.DP_NAME)

it works okee....
but i dont want 'null'...i want all to be written in my LOV and then make changes to the query....

Any ideas ?
what changes will be made to the query.

Thanks.
Charu.

[EDITED by LF: disabled smilies in this message]

[Updated on: Mon, 23 February 2009 02:16] by Moderator

Report message to a moderator

Re: parameter with value 'ALL' [message #388076 is a reply to message #388074] Mon, 23 February 2009 02:17 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, instead of
SELECT NULL FROM DUAL
UNION
...
use
SELECT 'All' dp_name FROM DUAL
UNION
...
Re: parameter with value 'ALL' [message #388079 is a reply to message #388076] Mon, 23 February 2009 02:20 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
that i did...
but how to i modify my query ??

i mean earlier it was:
where INV_DEPT.DP_NAME = NVL(:p,INV_DEPT.DP_NAME)


now what ??
its not null so i cant use nvl


thanks
charu

[EDITED by LF: disabled smilies once again]

[Updated on: Mon, 23 February 2009 02:31] by Moderator

Report message to a moderator

Re: parameter with value 'ALL' [message #388080 is a reply to message #388079] Mon, 23 February 2009 02:21 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
sorry i wrote :parameter (p)...it replaced with that smiley
Re: parameter with value 'ALL' [message #388082 is a reply to message #388079] Mon, 23 February 2009 02:36 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, as it won't be NULL again, NVL function won't help. Perhaps you should consider using the DECODE function instead, something like
where INV_DEPT.DP_NAME = DECODE(:parameter_value, 'All', inv_dept.dp_name,
                                                         :parameter_value
                               )


As of smilies in your messages: before pressing the "Submit Reply" button, I'd suggest you to "Spell-check Message" - it will both preview it and show all errors you have made. Doing so, you'd see a smiley and be able to disable it (see the screenshot), as well as fix spelling errors you have made. It will both make your messages much more readable.

./fa/5773/0/
Re: parameter with value 'ALL' [message #388085 is a reply to message #388082] Mon, 23 February 2009 02:46 Go to previous message
charuamit
Messages: 121
Registered: January 2009
Senior Member
thanks Littlefoot

It worked with decode Smile

Previous Topic: Two Parameter form in single report
Next Topic: Change the Initial Value in User Parameter Field
Goto Forum:
  


Current Time: Sun Dec 04 19:06:08 CST 2016

Total time taken to generate the page: 0.24575 seconds