Home » SQL & PL/SQL » SQL & PL/SQL » multiple report parameter values
multiple report parameter values [message #569162] Sun, 21 October 2012 14:27 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59997
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
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

icon14.gif  Re: multiple report parameter values [message #569213 is a reply to message #569164] Mon, 22 October 2012 18:15 Go to previous message
cherry3
Messages: 5
Registered: October 2012
Junior Member
Thank you so much for the answers. the above ways to pass multiple value to single parameter did work for me. I appreciate it.
Previous Topic: QUERY PROBLEM
Next Topic: Need to get value from one column but display id different fields.
Goto Forum:
  


Current Time: Sat Dec 20 08:51:54 CST 2014

Total time taken to generate the page: 0.09275 seconds