This discussion is archived
1 Reply Latest reply: Jan 5, 2009 12:35 PM by 429587 RSS

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

429587 Newbie
Currently Being Moderated
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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points