KyLim KyLim - 2 months ago 7
SQL Question

stored procedure looping error

im new to stored procedure but im getting error saying as below:
Subquery returned more than 1 value. This is not permitted when the subquery follows

My Table

E_R_Main is header table,E_R_Main is detail table.Both tables join using R_ID
My Expected Output :
enter image description here

DECLARE @LALA NVARCHAR(10);
DECLARE @LALA2 NVARCHAR(10);

CREATE TABLE #TEMP (EMP_NAME nvarchar(256) , Total_Pending nvarchar(256),Reschedule_Task nvarchar(256),New_Task nvarchar(256))
--select * from #TEMP

insert into #TEMP(EMP_NAME,Total_Pending) SELECT DISTINCT(PIC),COUNT(PIC) AS COUNTTT FROM E_R_DetailT A
inner join E_R_MainT b
on a.R_ID=b.R_ID
WHERE PIC IS NOT NULL
GROUP BY T_EMP_ID

SET @LALA = (select COUNT(B.R_ID) AS TEXSST from E_R_MainT A INNER JOIN E_R_DetailT B ON A.R_ID=B.R_ID Where (b.R_Y ='[Redo]' OR R_Y_N='[r]') and (STAT='S_5' or STAT='S_3') GROUP BY T_EMP_ID )
SET @LALA2 = (select distinct(B.T_EMP_ID) from E_R_MainT A INNER JOIN E_R_DetailT B ON A.R_ID=B.R_ID Where (b.R_Y ='[Redo]' OR R_Y_N='[r]') and (STAT='S_5' or STAT='S_3') GROUP BY T_EMP_ID )

UPDATE #TEMP SET Reschedule_Task = @LALA WHERE #TEMP.EMP_NAME=@LALA2

--SELECT * FROM #TEMP
SELECT B.Name,a.Total_Pending,New_Task,case when Reschedule_Task is null then 0 else Reschedule_Task end as User_Feedback FROM #TEMP A
INNER JOIN UserInfo B
ON A.EMP_NAME=B.EmployeeNo
drop table #TEMP

Answer

Your code seems way more complicated than needed to solve this problem. It looks like a JOIN with conditional aggregation:

select d.pic, 
       sum(case when m.status in ('S_1', 'S_3', 'S_4') then 1 else 0 end) as Pending,
       sum(case when m.status = 'S_1' then 1 else 0 end) as New,
       sum(case when m.status = 'S_3' then 1 else 0 end) as Reschedule,
       sum(case when m.status = 'S_4' then 1 else 0 end) as Completed
from e_r_main m join
     e_r_detail d
     on m.r_id = d.r_id
group by d.pic;
Comments