SELECT from table with Varying IN list in WHERE clause

Question

I am facing a issue in project I am working on, I can not give you actual code but I have created a executable sample code as below

Here temp and temp_id are two tables

1. temp table contains comma separated list of ids which is VARCHAR2
2. temp_id table contains actual ids which is NUMBER

I want to search rows from temp_id table by getting ids from comma separated list of ids from temp table

//DDLs to create table
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);

This Query doesn't work

SELECT * FROM temp_id WHERE data_id IN (SELECT to_number(COLUMN_VALUE) FROM XMLTABLE(SELECT ids FROM temp));

Working query

SELECT * FROM temp_id WHERE data_id IN (SELECT to_number(COLUMN_VALUE) FROM XMLTABLE('1, 2, 3'));

Here difference between above two queries is I am using column from temp table in first query and direct quoted varchar2 in second query. Not getting the reason why is not working? Is I am missing something? I think there might be some datatype mismatch but not able to figure it out.

Giles

'1, 2, 3' is not a valid number, its a string! – Fulgurating

I did to_number(COLUMN_VALUE). You can check the second query works. – Giles

It is a Varying IN lists problem. See my answer. – Broadminded

Modified the topic title to make it more explicit. – Broadminded

Answer 1

Your requirement is called as Varying IN-lists. See Varying IN list of values in WHERE clause

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

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

Solution :

For your requirement, you 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> 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

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 multiple rows. How you do it is up to you!

Working demo

Let's take an example of the standard EMP table in SCOTT schema.

I have a list of jobs in a string, and I want to count the employees for those jobs:

SQL> SET serveroutput ON
SQL> DECLARE
  2    str VARCHAR2(100);
  3    cnt NUMBER;
  4  BEGIN
  5    str := q'[CLERK,SALESMAN,ANALYST]';
  6    SELECT COUNT(*) INTO cnt FROM emp WHERE JOB IN (str);
  7    dbms_output.put_line('The total count is '||cnt);
  8  END;
  9  /
The total count is 0

PL/SQL procedure successfully completed.

Oh! What happened? The standard emp table should give an output 10. The reason is that the varying IN list.

Let's see the correct way:

SQL> SET serveroutput ON
SQL> DECLARE
  2    str VARCHAR2(100);
  3    cnt NUMBER;
  4  BEGIN
  5    str := q'[CLERK,SALESMAN,ANALYST]';
  6    SELECT COUNT(*)
  7    INTO cnt
  8    FROM emp
  9    WHERE job IN
 10      (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL))
 11      FROM dual
 12        CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
 13      );
 14    dbms_output.put_line('The total count is '||cnt);
 15  END;
 16  /
The total count is 10

PL/SQL procedure successfully completed.

Broadminded

Answer 2

There is another way in which this might be accomplished, namely by using LIKE:

SELECT ti.*
  FROM temp t, temp_id ti
 WHERE ',' || REPLACE(t.ids, ' ') || ',' LIKE '%,' || TO_CHAR(ti.data_id) || ',%'

(I used REPLACE() above to get rid of the extraneous whitespace in the ID list - makes things a bit simpler.) Alternately, one could use REGEXP_LIKE():

SELECT ti.*
  FROM temp t, temp_id ti
 WHERE REGEXP_LIKE(REPLACE(t.ids, ' '), '(^|,)' || TO_CHAR(ti.data_id) || '(,|$)')

[The caret (^) and dollar-sign ($) characters are to match the start and end, respectively, of the string -- so the ID can either match something at the start of the string (ending with a comma or the end of the string) or something starting with a comma (again, ending with a comma or the end of the string).]

Mesencephalon