So they have a stored procedure that has this:
This query returns anywhere from 1,500 - 2,500 rows.
Now they've added a new Exception table to the database which contains 11 - 12 people. If the userid in the query is in the Exception table, every column that has a CASE statement needs to be checked so that if its value is "A", "B" or "C" then return "EXCEPTION"; otherwise, leave as is.
Trying to use a BEGIN-END block for all of the CASE statement columns throws a syntax error. I'm beginning to think I'll have to use a cursor on the resultset, putting each row into separate variables, then iterate through it checking the userid and awardstatus variable. Then insert the variables into a table variable and finally do a Select on the table variable.
can anybody offer an easier way to do this?
SELECT U.UserID,
U.FirstName,
U.LastName,
|
V
(20 more columns)
|
V
,"HEADER A" =
CASE
WHEN (SELECT C2ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C2ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C2AwardStatus
WHEN (SELECT C3ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C3ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C3AwardStatus
WHEN (SELECT C4ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C4ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C4AwardStatus
WHEN (SELECT C5ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C5ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C5AwardStatus
WHEN (SELECT C6ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C6ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C6AwardStatus
WHEN (SELECT C7ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C7ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C7AwardStatus
WHEN (SELECT C8ID FROM TBLUserProgramPlan IUPP WHERE IUPP.C8ID = 7 AND IUPP.UserProgramPlanID = UPP.UserProgramPlanID) > 0 THEN C8AwardStatus
END
|
V
(a dozen more CASE statements)
|
V
FROM TBLUserProgramPlan UPP, TBLUser U, TBLUserProgramActivity UPA
WHERE UPP.UserProgramActivityID = UPA.UserProgramActivityID
AND U.UserID = UPA.UserID
AND U.EmployeeUSRID 'HRSYS'
AND U.UserStatus = 'ACTIVE'
AND UPA.ApprovalStatus = 'APPROVED'
AND ProgramStatus 'CLOSED'
AND UPA.ProgramID = 2
ORDER BY LastName, FirstName DESC
This query returns anywhere from 1,500 - 2,500 rows.
Now they've added a new Exception table to the database which contains 11 - 12 people. If the userid in the query is in the Exception table, every column that has a CASE statement needs to be checked so that if its value is "A", "B" or "C" then return "EXCEPTION"; otherwise, leave as is.
Trying to use a BEGIN-END block for all of the CASE statement columns throws a syntax error. I'm beginning to think I'll have to use a cursor on the resultset, putting each row into separate variables, then iterate through it checking the userid and awardstatus variable. Then insert the variables into a table variable and finally do a Select on the table variable.
can anybody offer an easier way to do this?