| DECODE - help!! [message #369751] |
Wed, 04 October 2000 04:53  |
LB
Messages: 13
Registered: September 2000
|
Junior Member |
|
|
Hi,
I'll try and be as clear as possible! I'm running a procedure within a report which takes in a list of codes as parameters. They can either include or exclude this list or include all codes. I need to check these codes in my cursor query where the table code is in or not in the list of codes supplied by the user.
Can I do this using decode? I have it working fine if I ignore the fact that they could also want to exclude them!
AND (CODE = DECODE(v_code1, 'ALL', CODE, v_code1)
OR CODE = DECODE(v_code2, NULL, v_code1, v_code2)
OR CODE = DECODE(v_code3, NULL, v_code1, v_code3)
OR CODE = DECODE(v_code4, NULL, v_code1, v_code4))
But how do I put in this extra check for <> ??? I also have an include/exclude parameter which I check for 'I' or 'E'.
Any help would be appreciated!
|
|
|
|
|
|
| Re: DECODE - help!! [message #369761 is a reply to message #369760] |
Thu, 05 October 2000 10:48  |
LB
Messages: 13
Registered: September 2000
|
Junior Member |
|
|
Okay, I'll try again!
This report is run from a form where the user can enter up to 4 codes (varchar2) to see on the report or can enter 'ALL' in the first code to get all codes. There is also the choice to include or exclude these 4 codes. I have no problem when I can create a lexical parameter for a sql query but in pl/sql I'm having trouble.
What I need in my query is
IF incl_excl_flag = 'I' THEN
SELECT fields
from tables
where CODE IN (list of codes)
ELSE
IF incl_excl_flag = 'E' THEN
select fields
from tables
where code NOT IN (list of codes)
I'm lost!!!
|
|
|
|
| Re: DECODE - help!! [message #369762 is a reply to message #369760] |
Thu, 05 October 2000 11:15 |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
select fields from tables
where (incl_excl_flag='I' and code in (listofcodes)) OR
(incl_excl_flag='E' and code NOT in (listofcodes))
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
