dynamic IN clause in SQL statement

Question

I have a SQL statement as shown below. In that i wanted to have IN clause values like 'X','Y','Z' and this value may vary based on different conditions. But when i give this value at run time SQL statement not giving me any output.

SELECT test,test1,test2
FROM test_n 
WHERE NVL(code,'X') in(:code);

here

:code = 'X','Y','Z'

Can any one help me?

Also i cannot use anonymous block or functions to achieve this.

You need dynamic SQL statement, or you need to pass the list from your application in a loop as separate variables into the `IN` clause — Kamil Gosciminski, Jul 02 '18 at 10:55
Can you mention what values you are getting from "code" column of table test_n? — Gaurav, Jul 02 '18 at 11:03

Kaushik Nayak · Answer 1 · 2018-07-02T11:19:05.320

You could use DEFINE by quoting the string ( substitution variables)

SET DEFINE ':'
DEFINE code  = "'X','Y','Z'"

SELECT test,test1,test2
FROM test_n 
WHERE NVL(code,'X') in(:code);

If you want to use true bind variables, you could try something like this with a LIKE statement.

set define off
VARIABLE code VARCHAR2
EXEC :code := 'X,Y,Z'
select test,test1,test2
from   test_n
where  ',' || :code || ',' like '%,' || code || ',%'
;

score 0 · Answer 2 · answered Jul 02 '18 at 11:10

You could use something like :

with tab as ( SELECT REGEXP_SUBSTR ('X,Y,Z', '[^,]+', 1, LEVEL) code FROM DUAL CONNECT BY REGEXP_SUBSTR ('X,Y,Z','[^,]+',1,LEVEL) IS NOT NULL) select test,test1,test2 from test_n where nvl(code,'X') in (select code from tab)

dynamic IN clause in SQL statement

2 Answers2