ORA-01427: single-row subquery returns more than one row (123)

Question

Hi can you help me solve my problem.

Need to know what ID that causes the error.

Possible duplicate of [ORA-01427: single-row subquery returns more than one row](http://stackoverflow.com/questions/17521985/ora-01427-single-row-subquery-returns-more-than-one-row) — hotzst, Jan 28 '16 at 07:09
I have like 20k records. need to know which id has been duplicated — user5850316, Jan 28 '16 at 07:13
why don't you search "how to eliminate duplicates in oracle"? — Florin Ghita, Jan 28 '16 at 15:09
Possible duplicate of [Removing duplicate rows from table in Oracle](http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) — Florin Ghita, Jan 28 '16 at 15:10

score 1 · Answer 1 · answered Jan 28 '16 at 07:39

This query will return all those rows the returns duplicated or just more then one row the meet the conditions.

SELECT B.TRCKG_GRP_REF_CDE
    , B.INTERNAL_SHMT_NUM
    FROM   CGO_CNTR_PACKG_RVW B
  , ORSDOC_950_CNTR_MAXSQ_GTT C
 WHERE    C.TRCKG_GRP_REF_CDE = B.CGO_TRCKG_GRP_REF_CDE
 AND    C.INTERNAL_SHMT_NUM = B.CGO_INTERNAL_SHMT_NUM
 AND    C.CNTR_MAX_SEQ_NUM = B.SEQ_NUM)
 group by  B.TRCKG_GRP_REF_CDE
    , B.INTERNAL_SHMT_NUM having count(*) > 1

Its your inner query, this ORA means that there is more then 1 row returning from it, and then you are comparing a column with more then one result.

Either you have to select distinct(incase of just duplicates) or you need to modify your query.

ORA-01427: single-row subquery returns more than one row (123)

1 Answers1