single-row subquery returns more than one row in sql

Question

select A.REGION  ,
       E.APPLY_ID    ,
       E.JOB_ID      , 
       E.LOGIN_ID    ,
       J.TITLE      
  from EMPLOYEE_JOBS E  ,EMPLOYEE_JOB_LIST  J ,EMPLOYEE_APP A ,COUNTRY C , LOV_MISC L ,LOV_MISC LL 
  where E.JOB_ID = J.JOB_ID
  AND   E.LOGIN_ID = A.LOGIN_ID
  AND   A.COUNTRY_UID = C.COUNTRY_ID
  AND   L.LOV_GRP ='EMP_HIRING'
  AND   L.LOV_CD  = E.STATUS
  AND   E.STATUS = '1'
  AND   LL.LOV_GRP ='EMP_SHIFT'
  AND   NVL(A.SHIFT_TIME,5) = LL.LOV_CD 
  AND   UPPER(A.gender) = decode(&V_GENDER,'BOTH',UPPER(A.gender), &V_GENDER )
  AND   A.REGION IN (case when &P_REGION IS NOT NULL  then (select column_value  from table (DOY_FN_STR_TO_TBL(&P_REGION)))
                          else A.REGION  end) ;

we are trying to get a set of record using case condition but finds the error below

ORA-01427 single-row subquery returns more than one row

Answer 1

select column_value  from table (DOY_FN_STR_TO_TBL(&P_REGION))

is the only sub-query in the question; exactly as the error states, it must be returning more than one row.

You need to restrict the sub-query so that it only returns a single row:

select column_value
from   table (DOY_FN_STR_TO_TBL(&P_REGION))
where  rownum = 1

Or else, rewrite the query to remove the CASE expression and use OR:

select A.REGION  ,
       E.APPLY_ID    ,
       E.JOB_ID      , 
       E.LOGIN_ID    ,
       J.TITLE      
from   EMPLOYEE_JOBS E
       INNER JOIN EMPLOYEE_JOB_LIST J
       ON (E.JOB_ID = J.JOB_ID)
       INNER JOIN EMPLOYEE_APP A
       ON (E.LOGIN_ID = A.LOGIN_ID)
       INNER JOIN COUNTRY C
       ON (A.COUNTRY_UID = C.COUNTRY_ID)
       INNER JOIN LOV_MISC L
       ON (L.LOV_CD  = E.STATUS)
       INNER JOIN LOV_MISC LL
       ON (NVL(A.SHIFT_TIME,5) = LL.LOV_CD)
where  L.LOV_GRP ='EMP_HIRING'
AND    E.STATUS = '1'
AND    LL.LOV_GRP ='EMP_SHIFT'
AND    (  &V_GENDER = 'BOTH'
       OR UPPER(A.gender) = &V_GENDER )
AND    (  A.REGION IN (select column_value  from table (DOY_FN_STR_TO_TBL(&P_REGION)))
       OR &P_REGION IS NULL );