CODEWITHSUNDEEP
sqloracle-databaseselectcountora-01427
Montaser El-sawy
Montaser El-sawy

Reputation: 830

ORA-01427: single-row subquery returns more than one row ,,WHEN USING SELECT COUNT

i have these two queries, both are same but the difference at the first line , first statment counts the result and the other get the rows, the problem is that it gets rows and can't count them

the first query gets error (ORA-01427: single-row subquery returns more than one row)

the first (count rows)

    SELECT  COUNT(*) FROM (
      SELECT A.* , 
      SUBSTR(A.RECORD_DATE,9,4) RECORD_NOTICE_TIME , 
      (SELECT B.CLOSE_FLAG FROM HARAM.NOTICES_DEPT B WHERE B.NOTICE_SN = A.NOTICE_SN AND B.DEPT_ID = 116 AND B.RECORD_STATUS <> 3) CLOSE_FLAG , 
      (SELECT DEPARTMENT_ID FROM HARAM.EMPLOYES WHERE PERSONAL_CODE =A.NOTICE_USER) NOTICE_USER_DEPT , 
      (SELECT SUBSTR(ND.CREATION_TIMESTAMP,9,4) TRANS_NOTICE_TIME FROM HARAM.NOTICES_DEPT ND WHERE ND.NOTICE_SN = A.NOTICE_SN AND ND.RECORD_STATUS != 3 AND ND.DEPT_ID = 116 ) TRANS_NOTICE_TIME,
      (SELECT  (SELECT DEPARTMENT_NAME FROM HARAM.DEPARTMENT WHERE DS.SECTION_ID=DEPARTMENT_ID) FROM HARAM.NOTICES_DEPT_SECTIONS DS WHERE DS.NOTICE_SN=A.NOTICE_SN) SECTION_NAME 
      FROM HARAM.NOTICES A 
      WHERE EXISTS (SELECT NULL FROM HARAM.NOTICES_DEPT C WHERE C.NOTICE_SN = A.NOTICE_SN AND C.RECORD_STATUS <> 3 AND C.DEPT_ID = 116 ) AND A.RECORD_STATUS <> 3 
    ) 
    WHERE NOTICE_STATUS != 5 AND RECORD_STATUS <> 3 AND CLOSE_FLAG=3 AND SECTION_NAME IS NOT NULL
    ORDER BY CLOSE_FLAG DESC , CREATION_TIMESTAMP DESC;

the second (get rows)

    SELECT * FROM (
      SELECT A.* , 
      SUBSTR(A.RECORD_DATE,9,4) RECORD_NOTICE_TIME , 
      (SELECT B.CLOSE_FLAG FROM HARAM.NOTICES_DEPT B WHERE B.NOTICE_SN = A.NOTICE_SN AND B.DEPT_ID = 116 AND B.RECORD_STATUS <> 3) CLOSE_FLAG , 
      (SELECT DEPARTMENT_ID FROM HARAM.EMPLOYES WHERE PERSONAL_CODE =A.NOTICE_USER) NOTICE_USER_DEPT , 
      (SELECT SUBSTR(ND.CREATION_TIMESTAMP,9,4) TRANS_NOTICE_TIME FROM HARAM.NOTICES_DEPT ND WHERE ND.NOTICE_SN = A.NOTICE_SN AND ND.RECORD_STATUS != 3 AND ND.DEPT_ID = 116 ) TRANS_NOTICE_TIME, 
      (SELECT (SELECT DEPARTMENT_NAME FROM HARAM.DEPARTMENT WHERE DS.SECTION_ID=DEPARTMENT_ID) FROM HARAM.NOTICES_DEPT_SECTIONS DS WHERE DS.NOTICE_SN=A.NOTICE_SN) SECTION_NAME 
      FROM HARAM.NOTICES A  
      WHERE EXISTS (SELECT NULL FROM HARAM.NOTICES_DEPT C WHERE C.NOTICE_SN = A.NOTICE_SN AND C.RECORD_STATUS <> 3 AND C.DEPT_ID = 116 ) AND A.RECORD_STATUS <> 3 
    ) 
    WHERE NOTICE_STATUS != 5 AND RECORD_STATUS <> 3 AND CLOSE_FLAG=3 AND SECTION_NAME IS NOT NULL
    ORDER BY CLOSE_FLAG DESC,CREATION_TIMESTAMP DESC;

Upvotes: 0

Views: 543

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You might need to replace your INNER Select with one below. I converted it as Join and used ROWNUM < =1 to limit the rows to maximum of 1.

(SELECT
  D.DEPARTMENT_NAME
FROM HARAM.NOTICES_DEPT_SECTIONS DS, HARAM.DEPARTMENT D
WHERE DS.NOTICE_SN=A.NOTICE_SN
 AND DS.SECTION_ID=D.DEPARTMENT_ID
 AND ROWNUM <= 1
) SECTION_NAME

Upvotes: 1

Related Questions