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)))

The error is self-explanatory: one of the subqueries is returning more than one row. You are best suited to inspect your business logic and modify the query. — Tim Biegeleisen
@TimBiegeleisen is right (self explanatory message). I would add as a bonus this: Are you sure you want to have REGEXP on columns of your source table(s)? If the table(s) is small, then it should be ok. Otherwise, you are inviting full scan on the table(s). — FDavidov
If I were a betting man I'd say that SELECT DISTINCT syscd subquery is the culprit, since it's the only one I see in which you need a single row result. To help you fix it you'd need to explain what you want it to do. — Deltharis
If i remove this statement "(decode (REGEXP_COUNT ('TEST1,TEST2', ','),0,'TEST3', " then the query is running, there is issue with decode — Kunal Sharma

J. Chomel J. Chomel · Accepted Answer · 2016-10-10T15:18:25

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...

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

1 Answers

EDIT