I want to pass multiple values via a single parameter.
select
id,
item_number
from AGILE.item_p2p3_query where subclass='2477110' and date32 is not null
and item_number in : p **// passing one parameter would work. But when I pass two parameters like EN 60439-1:1999,EN 60439-3:1991 doesn't seem to work**
--in ('EN 60439-3:1991','EN 60439-1:1999') // this will work
Kindly suggest only SQL suggestion and not PL/SQL as I would be using this in a report.
I assume you are binding the values to the query, as it works when there is one value, but fails when there are two. This is the how-do-I-bind-a-variable-in-list problem. There are a few solutions, but the one I like, that involves no PLSQL is:
with id_generator
as
(
SELECT regexp_substr(:txt, '[^,]+', 1, LEVEL) token
FROM dual
CONNECT BY LEVEL <= length(:txt) - length(REPLACE(:txt, ',', '')) + 1
)
select u.id, u.username
from users u, id_generator g
where u.id = g.token;
Bind your comma separated string as the value for :txt, and then structure your query as a join.
Full explanation - http://betteratoracle.com/posts/20-how-do-i-bind-a-variable-in-list
This may help - pas any valid list of deptnos lk 10,20...:
Select * From scott.emp
Where deptno IN (&deptno)
/
If passing a string then use quotes ...IN ('&ename') Thanks.
