Error - single-row subquery returns more than one row

Question

I've created a table type to pass multiple employee id's to my procedure. However, there is a requirement of, if nothing is passed to input parameter then all the employees should be retrieved.

Table type created -

CREATE OR REPLACE TYPE EMP_LIST IS TABLE OF NUMBER;  

I wrote below query to check the null value but it failed with error - ORA-01427: single-row subquery returns more than one row

SELECT COL_LIST FROM  EMP
WHERE EMP_ID IN (CASE WHEN (SELECT COLUMN_VALUE FROM table(EMP_LIST())) IS NULL 
                THEN (SELECT EMP_ID FROM EMP) 
                ELSE (SELECT COLUMN_VALUE FROM table(EMP_LIST())) END)

CASE cannot return multiple values. Could someone please suggest an alternative or any other solution to handle such scenario?

Thank You.

Answer 1

If you want to use this type you can write:

with t as (SELECT COLUMN_VALUE cv FROM table(EMP_LIST()))
SELECT COL_LIST FROM EMP
  where emp_id in (select cv from t) 
     or (select count(1) from t) = 0

^dbfiddle

Edit - explanation:

with t as (SELECT COLUMN_VALUE cv FROM table(EMP_LIST()))

This part is called CTE (common table expression). It is typically used when subquery is needed in main query twice or more, like here.

  where emp_id in (select cv from t) 

like in your attempt (first use of CTE)

     or (select count(1) from t) = 0

second use of CTE. WHERE condition together allows us to see rows which are in list provided in CTE or all rows if count of provided numbers is zero.

You can do that without CTE, but this way you had to define list twice.

Answer 2

Try something like this in your predicate

((select count(0) from table(emp_list()))=0 and emp_id IN (
                SELECT
                    emp_id
                FROM
                    emp
            )
            )
 or emp_id in (
                SELECT
                    column_value
                FROM
                    TABLE ( emp_list() )
            )