ORA-01427: single-row subquery returns more than one row for the below Query

0
votes

When i execute the following query in sql developer, i'm getting the following error message "Ora-01427 single-row subquery returns more than one row" 

select TPNATIVENAME as PTPNAME,
       INTERFACENAME as CTPNAME,
       DIRECTIONALITY,
       NENAME,
       NCNAME
from TP ,
  (select DIRECTIONALITY
   from SNCCOMPONENT
   where SNCID =
       (select unique SNCID
        from AFFECTEDXCS
        where PXCID = -124)),
  (select NODE.NAME as NENAME,
          NC.NAME as NCNAME
   from NODE NODE,
        NETWORKCONTROLLER NC
   where NODE.HANDLE =
       (select unique SRCNEID
        from SNCCOMPONENT
        where SNCID =
            (select unique SNCID
             from AFFECTEDXCS
             where PXCID = -124))
     and NC.HANDLE = NODE.NCHANDLE)
where NEID =
    (select SRCNEID
     from SNCCOMPONENT
     where SNCID =
         (select unique SNCID
          from AFFECTEDXCS
          where PXCID = -124))
  and TPID =
    (select SRCTP
     from SNCCOMPONENT
     where SNCID =
         (select unique SNCID
          from AFFECTEDXCS
          where PXCID = -124))
union
select TPNATIVENAME as PTPNAME,
       INTERFACENAME as CTPNAME,
       DIRECTIONALITY,
       NENAME,
       NCNAME
from TP,
  (select DIRECTIONALITY
   from SNCCOMPONENT
   where SNCID =
       (select unique SNCID
        from AFFECTEDXCS
        where PXCID = -124)),
  (select NODE.NAME as NENAME,
          NC.NAME as NCNAME
   from NODE NODE,
        NETWORKCONTROLLER NC
   where NODE.HANDLE =
       (select unique SRCNEID
        from SNCCOMPONENT
        where SNCID =
            (select unique SNCID
             from AFFECTEDXCS
             where PXCID = -124))
     and NC.HANDLE = NODE.NCHANDLE)
where NEID =
    (select SINKNEID
     from SNCCOMPONENT
     where SNCID =
         (select unique SNCID
          from AFFECTEDXCS
          where PXCID = -124))
  and TPID =
    (select SINKTP
     from SNCCOMPONENT
     where SNCID =
         (select unique SNCID
          from AFFECTEDXCS
          where PXCID = -124));

I'm not getting which inner query is returning multiple values and how to solve this error?

2
sqloracle
You'd need to narrow down the problem. Take each individual subquery on its own and determine whether it ever returns multiple rows. Unless you're going to give us a reproducible test case, we can't really do much to debug the issue. – Justin Cave
her you can find an sql formatter: sqlformat.org – miracle173

2 Answers

1
votes
SELECT UNIQUE X FROM Y

This query does not guarantee that your query will return single-row. It may return multiple rows. It only guarantees that it won't return the same X more than once.

So, you need to use IN instead of =.

For example:

SELECT SINKTP FROM SNCCOMPONENT
WHERE SNCID IN (SELECT UNIQUE SNCID FROM AFFECTEDXCS WHERE PXCID = -124)
0
votes

Make sure these SQL gives you only one value:

SELECT UNIQUE SNCID
  FROM AFFECTEDXCS
 WHERE PXCID = -124

SELECT SRCNEID
  FROM SNCCOMPONENT
 WHERE SNCID = (
                SELECT UNIQUE SNCID
                  FROM AFFECTEDXCS
                 WHERE PXCID = -124
               )

SELECT UNIQUE SRCNEID
  FROM SNCCOMPONENT
 WHERE SNCID = (
                SELECT UNIQUE SNCID
                  FROM AFFECTEDXCS
                 WHERE PXCID = -124
               )

SELECT SINKNEID
  FROM SNCCOMPONENT
 WHERE SNCID = (
                SELECT UNIQUE SNCID
                  FROM AFFECTEDXCS
                 WHERE PXCID = -124
               )

SELECT SINKTP
  FROM SNCCOMPONENT
 WHERE SNCID = (
                SELECT UNIQUE SNCID
                  FROM AFFECTEDXCS
                 WHERE PXCID = -124
               )

And if one (or more) returns you more than one rows, as a quick solution, you can add the the rownum < 2 predicate to the where clause or replace exact the = sign with the IN word.