parameter with value 'ALL' [message #388074] |
Mon, 23 February 2009 02:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #388082 is a reply to message #388079] |
Mon, 23 February 2009 02:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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.
|
|
|
|