Home » SQL & PL/SQL » SQL & PL/SQL » multiple report parameter values
| multiple report parameter values [message #569162] |
Sun, 21 October 2012 14:27  |
 |
cherry3
Messages: 5
Registered: October 2012
|
Junior Member |
|
|
Hi All,
For the below query:
SELECT id, pob,exp
FROM emp
where exp= (:exp)or @exp is null
O/P:
id pob exp
_____________________
1 CT 2
2 NJ 3
3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values
Any help would be appreciated.
Thanks
|
|
|
|
| Re: multiple report parameter values [message #569163 is a reply to message #569162] |
Sun, 21 October 2012 15:07  |
 |
Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
Here's an example:
SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'
PL/SQL procedure successfully completed.
SQL> select substr(:mylist,
2 instr(','||:mylist||',', ',', 1, rn),
3 instr(','||:mylist||',', ',', 1, rn+1)
4 - instr(','||:mylist||',', ',', 1, rn) - 1) value
5 from ( select rownum rn from dual
6 connect by level
7 <= length(:mylist)-length(replace(:mylist,',',''))+1
8 )
9 /
VALUE
-----------------------------------------------------------
5
11
13
22
23
31
44
45
SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'
SQL> with list as (
2 select substr(:mylist,
3 instr(','||:mylist||',', ',', 1, rn),
4 instr(','||:mylist||',', ',', 1, rn+1)
5 - instr(','||:mylist||',', ',', 1, rn) - 1) value
6 from (select rownum rn from dual
7 connect by level
8 <= length(:mylist)-length(replace(:mylist,',',''))+1)
9 )
10 select id, valeur
11 from t
12 where id IN ( select value from list )
13 order by id
14 /
ID USERNAME
---------- ------------------------------
5 SYSTEM
11 OUTLN
22 MICHEL
23 OPS$MCADOT101205
31 SCOTT
5 rows selected.
Regards
Michel
|
|
|
|
| Re: multiple report parameter values [message #569164 is a reply to message #569163] |
Sun, 21 October 2012 15:15 |
Solomon Yakobson
Messages: 1407
Registered: January 2010
|
Senior Member |
|
|
Or, if we are looking for direct match, plain LIKE will do:
SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'
PL/SQL procedure successfully completed.
SQL> select object_id,
2 owner,
3 object_name,
4 object_type
5 from dba_objects
6 where ',' || :mylist || ',' like '%,' || object_id || ',%'
7 /
OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------ -------------------
44 SYS I_USER1 INDEX
13 SYS UET$ TABLE
5 SYS CLU$ TABLE
23 SYS PROXY_DATA$ TABLE
22 SYS USER$ TABLE
31 SYS CDEF$ TABLE
45 SYS I_COL1 INDEX
11 SYS I_USER# INDEX
8 rows selected.
SQL>
SY.
[Updated on: Sun, 21 October 2012 18:17] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat May 25 12:13:18 CDT 2013
Total time taken to generate the page: 0.13706 seconds
|
] 
Members
Search
Help
Register
Login
Home
