One of the frequent question on Oracle forums is about the Varying IN list in WHERE clause. One such question was asked here http://stackoverflow.com/questions/27738359/select-from-table-with-varying-in-list-in-where-clause/27739139#27739139.
Let’s understand the issue.
Test case :
//DDLs to create the test tables
CREATE TABLE temp(ids VARCHAR2(4000));
CREATE TABLE temp_id(data_id NUMBER);
//DMLs to populate test data
INSERT INTO temp VALUES('1, 2, 3');
INSERT INTO temp_id VALUES(1);
INSERT INTO temp_id VALUES(2);
INSERT INTO temp_id VALUES(3);
INSERT INTO temp_id VALUES(4);
INSERT INTO temp_id VALUES(5);Reason :
IN (‘1, 2, 3’) is NOT same as IN (1, 2, 3) OR IN(‘1’, ‘2’, ‘3’)
Hence,
SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);
is same as
SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');
which would thrown an error ORA-01722: invalid number –
SQL> SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');
SELECT * FROM temp_id WHERE data_id IN('1, 2, 3')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);
SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp)
*
ERROR at line 1:
ORA-01722: invalid number
Above is NOT same as
SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);
which would give you correct output –
SQL> SELECT * FROM temp_id WHERE data_id IN(1, 2, 3); DATA_ID ---------- 1 2 3 SQL>
So, ‘1, 2, 3’ is a single string, a single value in that in list.
Solution :
For such requirement, we can achieve it like this –
SQL> SELECT * FROM temp; IDS -------------------------------------------------------------- 1, 2, 3 SQL> SELECT * FROM temp_id; DATA_ID ---------- 1 2 3 4 5 SQL> SQL> WITH data AS 2 (SELECT to_number(trim(regexp_substr(ids, '[^,]+', 1, LEVEL))) ids 3 FROM temp 4 CONNECT BY instr(ids, ',', 1, LEVEL - 1) > 0 5 ) 6 SELECT * FROM temp_id WHERE data_id IN 7 (SELECT ids FROM data 8 ) 9 / DATA_ID ---------- 1 2 3 SQL>
Alternatively, you can create your own TABLE function or a Pipelined function to achieve this. Your goal should be to split the comma-separated IN list into different rows. How you do it is up to you!
Lalit Kumar B 