getting " SQL Error: ORA-01427: single-row subquery returns more than one row " in the below query

0
votes

I'm getting the error " SQL Error: ORA-01427: single-row subquery returns more than one row " in this query 

UPDATE IN_DOC_HDR INDOC
SET INDOC.DOC_CLSD_DT = (
  SELECT PRHDR.DOC_CLSD_DT
  FROM PR_DOC_HDR PRHDR
   , PR_DOC_COMM PRCOMM
  WHERE INDOC.DOC_ACTU_AM <> 0 
      AND INDOC.DOC_ACTU_AM <> INDOC.DOC_CLSD_AM 
      AND PRCOMM.IN_DOC_CD = INDOC.DOC_CD 
      AND PRCOMM.IN_DOC_DEPT_CD = INDOC.DOC_DEPT_CD 
      AND PRCOMM.IN_DOC_ID = INDOC.DOC_ID 
      AND PRCOMM.DOC_PHASE_CD = 3 
      AND PRHDR.DOC_CD = PRCOMM.DOC_CD 
      AND PRHDR.DOC_DEPT_CD = PRCOMM.DOC_DEPT_CD 
      AND PRHDR.DOC_ID = PRCOMM.DOC_ID
  )
WHERE EXISTS (
  SELECT 1
  FROM IN_DOC_HDR INDOC
   , PR_DOC_COMM PRCOMM
   , PR_DOC_HDR PRHDR
  WHERE INDOC.DOC_ACTU_AM <> INDOC.DOC_CLSD_AM 
      AND INDOC.DOC_ACTU_AM <> 0 
      AND PRCOMM.IN_DOC_CD = INDOC.DOC_CD 
      AND PRCOMM.IN_DOC_DEPT_CD = INDOC.DOC_DEPT_CD 
      AND PRCOMM.IN_DOC_ID = INDOC.DOC_ID 
      AND PRCOMM.DOC_PHASE_CD = 3 
      AND PRHDR.DOC_CD = PRCOMM.DOC_CD 
      AND PRHDR.DOC_DEPT_CD = PRCOMM.DOC_DEPT_CD 
      AND PRHDR.DOC_ID = PRCOMM.DOC_ID
  )
2
oracle
This seems pretty self-explanatory... Your single-row subquery is returning more than one row... – ZLK
Please tag the relevant dbms. Your question is not related to sql server. – SMor
mmm, alert, how will you know you get the right value? limit by rownum "might" work or it might not... test – Paul Maxwell
Welcome to SO, as this is your first question you may benefit from help center looking for "asking" and "accepting" as topics – Paul Maxwell

2 Answers

0
votes

For this error, you should limit your data to 1 row by using ROWNUM.Tru this may this works.

UPDATE IN_DOC_HDR INDOC SET INDOC.DOC_CLSD_DT = (SELECT PRHDR.DOC_CLSD_DT from PR_DOC_HDR PRHDR ,PR_DOC_COMM PRCOMM WHERE 
INDOC.DOC_ACTU_AM <>0
AND INDOC.DOC_ACTU_AM <> INDOC.DOC_CLSD_AM AND PRCOMM.IN_DOC_CD=INDOC.DOC_CD AND PRCOMM.IN_DOC_DEPT_CD=INDOC.DOC_DEPT_CD AND
PRCOMM.IN_DOC_ID = INDOC.DOC_ID AND PRCOMM.DOC_PHASE_CD = 3 AND PRHDR.DOC_CD = PRCOMM.DOC_CD AND PRHDR.DOC_DEPT_CD = PRCOMM.DOC_DEPT_CD
AND PRHDR.DOC_ID=PRCOMM.DOC_ID WHERE ROWNUM <= 1) WHERE EXISTS 
(SELECT 1 FROM IN_DOC_HDR INDOC,PR_DOC_COMM PRCOMM,PR_DOC_HDR PRHDR WHERE INDOC.DOC_ACTU_AM <> INDOC.DOC_CLSD_AM AND 
INDOC.DOC_ACTU_AM <>0 AND PRCOMM.IN_DOC_CD=INDOC.DOC_CD AND PRCOMM.IN_DOC_DEPT_CD=INDOC.DOC_DEPT_CD AND
PRCOMM.IN_DOC_ID = INDOC.DOC_ID AND PRCOMM.DOC_PHASE_CD = 3 AND PRHDR.DOC_CD = PRCOMM.DOC_CD AND PRHDR.DOC_DEPT_CD = PRCOMM.DOC_DEPT_CD
AND PRHDR.DOC_ID=PRCOMM.DOC_ID)
0
votes

How many rows can this return? = more than one

  SELECT PRHDR.DOC_CLSD_DT
  FROM PR_DOC_HDR PRHDR
   , PR_DOC_COMM PRCOMM
  WHERE INDOC.DOC_ACTU_AM <> 0 
      AND INDOC.DOC_ACTU_AM <> INDOC.DOC_CLSD_AM 
      AND PRCOMM.IN_DOC_CD = INDOC.DOC_CD 
      AND PRCOMM.IN_DOC_DEPT_CD = INDOC.DOC_DEPT_CD 
      AND PRCOMM.IN_DOC_ID = INDOC.DOC_ID 
      AND PRCOMM.DOC_PHASE_CD = 3 
      AND PRHDR.DOC_CD = PRCOMM.DOC_CD 
      AND PRHDR.DOC_DEPT_CD = PRCOMM.DOC_DEPT_CD 
      AND PRHDR.DOC_ID = PRCOMM.DOC_ID

Now consider you are using this in an UPDATE which works "row by row"

On a single row, INDOC.DOC_CLSD_DT can only store a single value

So how, if the query can return many values, will it choose which value to put into INDOC.DOC_CLSD_DT ?

It cannot make that decision, and it stops (correctly) and gives you the error message you see before you.

You need to test this much more thoroughly before updating the table.