Select from Table with Varying in List in Where Clause

SELECT from table with Varying IN list in WHERE clause

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.

Select all rows by variable in WHERE clause in SQL Server

The canonical way would be to allow @myBool to take on NULL value:

WHERE myBool = @myBool OR @myBool IS NULL

I should note that this doesn't optimize well. But, if you have just two values, then an index is probably not going to be used anyway.

PLSQL - IN clause using variable with multiple values

Declare the type globally:

CREATE TYPE number_list IS TABLE OF NUMBER;

Then you can initialise the list and then, in each iteration of the loop, EXTEND the list and assign the value and, finally, use the MEMBER OF operator or IN with a subquery and table collection expression:

DECLARE
  l_id_list NUMBER_LIST := NUMBER_LIST();
BEGIN
  FOR i IN 1..l_row_count
  LOOP
    l_id_list.EXTEND;
    l_id_list(l_id_list.COUNT) := to_number(
                                    apex_json.get_varchar2(
                                      p_path   => 'rows[%d].id', 
                                      p0       => i,
                                      p_values => l_values
                                    )
                                  );
  END LOOP;

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid MEMBER OF l_id_list;

  -- or

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid IN (SELECT COLUMN_VALUE FROM TABLE(l_id_list));
END;

SQL if statement to select items form different tables

You're not going to be able to embed an IF within a CTE or a Create-Table-As.

An alternative structure can be to union two queries with mutually exclusive WHERE clauses... (Such that only one of the two queries ever returns anything.)

For example, if the code below, something is checked for being NULL or NOT NULL, and so only one of the two can ever return data.

WITH
  info AS
(
  SELECT
    Date AS Day,
    Box,
  FROM
    `table_1`
),
  other_info AS
(
  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  THIS BIT
  --------------------------------------------------------------------------------
  SELECT
    Date AS Day,
    Box
  FROM
    `table_1`
  WHERE
    (SELECT MAX(x) FROM y) IS NULL

  UNION ALL

  SELECT
    Date AS Day,
    Box
  FROM
    `table_2`
  WHERE
    (SELECT MAX(x) FROM y) IS NOT NULL
  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
)
SELECT
  Date
  Box
  Box_description
FROM
  `table_3`
  LEFT JOIN info(Day)
  LEFT JOIN other_info(Day)

Passing a DataFrame list to a WHERE clause in a SQL query embedded in R

Suppose we wish to insert the Time column from the built in data frame BOD into an sql query.

sprintf("select * from X where Y in (%s)", toString(BOD$Time))
## [1] "select * from X where Y in (1, 2, 3, 4, 5, 7)"

PL/SQL - Use List Variable in Where In Clause

Use a collection:

CREATE TYPE Varchar2TableType AS TABLE OF VARCHAR2(200);

Or use a built-in type like SYS.ODCIVARCHAR2LIST or SYS.ODCINUMBERLIST:

VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 100 );

  your_collection(  1) := 'Some value';
  your_collection(  2) := 'Some other value';
  -- ...
  your_collection(100) := DBMS_RANDOM.STRING( 'x', 20 );

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

PRINT cursor;

---

Related Topics