ORA-01427: single-row subquery returns more than one row is SQL query

Question

Below is my sql query and I am getting error while executing it as ORA-01427: single-row subquery returns more than one row.

Please help with a work around:

SELECT *
FROM TableName err
WHERE ERR.SYSCD IN
      (decode(REGEXP_COUNT('TEST1,TEST2', ','), 0, 'TEST3',
              (SELECT DISTINCT syscd
               FROM TableName
               WHERE syscd IN
                     (SELECT err.syscd
                      FROM TableName ERR
                      WHERE ERR.SYSCD NOT IN
                            (SELECT UPPER(
                                        REGEXP_SUBSTR(
                                            'TEST1',
                                            '[^,]+',
                                            1,
                                            LEVEL))
                              CNTRY_CD
                             FROM DUAL
                             CONNECT BY REGEXP_SUBSTR(
                                            'TEST1',
                                            '[^,]+',
                                            1,
                                            LEVEL)
                                        IS NOT NULL)
                     )
                     OR syscd IS NULL)))

Answer 1

Its a simple misconception. Here is how decode works:

select decode(a, 'a', 'AA', 'b','BB', '??') from (
           select 'a' a from dual 
 union all select 'b' a from dual 
 union all select 'c' a from dual
);

gives

AA
BB
??

This is because every operand in decode is a single value. But in your query, it is obvious your

SELECT DISTINCT syscd
           FROM TableName
           WHERE syscd IN (<some values set>)
                 OR syscd IS NULL

returns more than one row. So to solve it, you should ensure there is only one row with a WHERE rownum=1:

SELECT *
FROM TableName err
WHERE ERR.SYSCD IN
      (decode(REGEXP_COUNT('TEST1,TEST2', ','), 0, 'TEST3',
              select val from (SELECT DISTINCT syscd val
               FROM TableName
               WHERE syscd IN
                     (SELECT err.syscd
                      FROM TableName ERR
                      WHERE ERR.SYSCD NOT IN
                            (SELECT UPPER(
                                        REGEXP_SUBSTR(
                                            'TEST1',
                                            '[^,]+',
                                            1,
                                            LEVEL))
                              CNTRY_CD
                             FROM DUAL
                             CONNECT BY REGEXP_SUBSTR(
                                            'TEST1',
                                            '[^,]+',
                                            1,
                                            LEVEL)
                                        IS NOT NULL)
                     )
                     OR syscd IS NULL) where rownum=1))

Be careful, because this might not do what you expect. It only takes one value from the sub-select, but not sure it is what you expect.

EDIT

It makes no sense to do the above for you. What you want, is have either syscd in <one set>, and possibly in other values from your decode. So make an union between them; Here:

SELECT *
FROM TableName err
WHERE ERR.SYSCD IN (
              select decode(REGEXP_COUNT('TEST1,TEST2', ','), 0, 'TEST3') 
                from dual
      union
              SELECT DISTINCT syscd
               FROM TableName
               WHERE syscd IN
                     (SELECT err.syscd
                      FROM TableName ERR
                      WHERE ERR.SYSCD NOT IN
                            (SELECT UPPER(
                                        REGEXP_SUBSTR(
                                            'TEST1',
                                            '[^,]+',
                                            1,
                                            LEVEL))
                              CNTRY_CD
                             FROM DUAL
                             CONNECT BY REGEXP_SUBSTR(
                                            'TEST1',
                                            '[^,]+',
                                            1,
                                            LEVEL)
                                        IS NOT NULL)
                     )
                     OR syscd IS NULL) 

... but it still doesn't make much sense...