1 Reply Latest reply: Jan 5, 2009 2:35 PM by 429587 RSS

    ORA-01427: single-row subquery returns more than one row

    429587
      Helllo Everyone,

      I have been trying to get this query, subquery to work, but I keep getting this error: ORA-01427: single-row subquery returns more than one row. The query is returning multiple rows, so, I need to some how do a group by, but I am having problems getting it to work. This is my query below can anyone help me. Thanks.


      SELECT op_code,lco_num,
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE op_code = 08
      GROUP BY op_code,lco_num) "PI_Workload",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_RELEASED_TO_FLD IS NOT NULL
      AND op_code = 08
      GROUP BY op_code,lco_num) "PI_Case_Assigned",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_RELEASED_TO_FLD IS NOT NULL
      AND DATE_RECEIVED IS NULL
      AND op_code = 08
      GROUP BY op_code,lco_num) "PI_Case_on_Laptop",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_CHECKED_IN IS NULL
      AND IN_SUPV_REVIEW = 1
      AND op_code = 08
      GROUP BY op_code,lco_num) "PI_Case_In_Supervisory_Review" ,
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_CHECKED_IN IS NOT NULL
      AND op_code = 08
      GROUP BY op_code,lco_num) "PI Case_Checked_In",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE OUTCOME_CODE IN ('201','203','208','209')
      AND op_code = 08
      GROUP BY op_code,lco_num) "Completes_Partials",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE OUTCOME_CODE IN ('213','216','218','219')
      AND op_code = 08
      GROUP BY op_code,lco_num) "Type_A",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE OUTCOME_CODE IN ('326','327')
      AND op_code = 08
      GROUP BY op_code,lco_num) "Type_B",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE OUTCOME_CODE IN ('333','334')
      AND op_code = 08
      GROUP BY op_code,lco_num) "Type_C",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_CHECKED_IN IS NULL
      AND op_code = 08
      GROUP BY op_code,lco_num) "PI_Case_Remaining",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE op_code = 09
      GROUP BY op_code,lco_num) "RI_Workload_To_Date",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE REINT_TYPE = 'R'
      AND op_code = 09
      GROUP BY op_code,lco_num) "Random_Workload",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE REINT_TYPE = 'S'
      AND op_code = 09
      GROUP BY op_code,lco_num) "Supplemental_Workload",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE REINT_TYPE = 'O'
      AND op_code = 09
      GROUP BY op_code,lco_num) "Outlier_Workload",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE REINT_TYPE = 'X'
      AND op_code = 09
      GROUP BY op_code,lco_num) "Hard_Fail_Workload",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_RELEASED_TO_FLD IS NOT NULL
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI_Case_Assigned",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_RELEASED_TO_FLD IS NOT NULL
      AND DATE_RECEIVED IS NULL
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI_Case_on_Laptop",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_CHECKED_IN IS NULL
      AND IN_SUPV_REVIEW = 1
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI_Case_In_Supervisory_Review",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_CHECKED_IN IS NOT NULL
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI Case_Checked_In",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE OUTCOME_CODE IN ('201','203')
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI_Completes_Partials",
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE OUTCOME_CODE IN ('213','214','219')
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI_Noninterviews" ,
      (SELECT COUNT (*)
      FROM CASE_CONTROL_VW
      WHERE DATE_CHECKED_IN IS NULL
      AND op_code = 09
      GROUP BY op_code,lco_num) "RI_Case_Remaining"
      FROM CASE_CONTROL_VW
      GROUP BY op_code,lco_num;