Answered by:
Case statement in where clause

Question
-
I'm trying to create a SSRS report for pulling employee records.
Depending on the parameter value for @workgroup at run-time, I want the query to select records based on specific status. I tried the below case statement in where clause but it is not working.
-------------------------------------------
FROM EMPLOYEE E LEFT OUTER JOIN DEPT D ON E.DEPT = D.CODE LEFT OUTER JOIN ORGUNIT O ON O.CODE = E.ORGUNIT LEFT OUTER JOIN JOB J ON E.JOB = J.CODE WHERE E.FLDWORKGRP = @workgroup AND CASE WHEN @workgroup = 'Value' THEN E.STATUS = 'A' ELSE E.STATUS IN ('A','N','LOA','P','D') END ORDER BY E.LNAME
Let me know if there is any other way to do this. Thanks in advance for your help.
Wednesday, December 3, 2014 6:33 PM
Answers
-
I'm sorry for all the confusion, finally I was able to make it work. In future, I will make sure to describe the logic I want to implement in detail.
WHERE
E.FLDWORKGRP = @workgroup AND
1 = (CASE
WHEN @workgroup = 'UWR' AND E.FLDSTATUS = 'A' THEN 1
ELSE CASE WHEN @workgroup = 'UWMCMS' AND E.FLDSTATUS IN ('A','N','LOA','P','D') THEN 1 ELSE 0 END END)Thanks for all the helpful scripts and suggestions.
- Marked as answer by anishap456 Wednesday, December 3, 2014 9:32 PM
Wednesday, December 3, 2014 9:32 PM
All replies
-
FROM dbo.EMPLOYEE E LEFT OUTER JOIN dbo.DEPT D ON E.DEPT = D.CODE LEFT OUTER JOIN dbo.ORGUNIT O ON O.CODE = E.ORGUNIT LEFT OUTER JOIN dbo.JOB J ON E.JOB = J.CODE WHERE E.FLDWORKGRP = @workgroup AND 1 = (CASE WHEN @workgroup = 'Value' THEN (CASE WHEN E.STATUS = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.STATUS IN ('A','N','LOA','P','D') THEN 1 ELSE 0 END) END) ORDER BY E.LNAME
Please let me know if above SQL statement works for you or not.Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.
- Proposed as answer by Jason A Long Wednesday, December 3, 2014 7:18 PM
- Unproposed as answer by Jason A Long Wednesday, December 3, 2014 7:18 PM
Wednesday, December 3, 2014 6:43 PM -
No, it didn't worked.
Thanks for your quick reply.
Wednesday, December 3, 2014 6:52 PM -
Could you please put some sample data and describe what you are trying to achieve and desired output?
I did the below test and seems is working, that is why if you can provide us what you pass as parameter with some sample data would be great.
DECLARE @Employee Table (EmployeeID INT, Status CHAR(3), FLDWORKGRP VARCHAR(10)) INSERT INTO @Employee SELECT 1, 'A', 'Value' UNION ALL SELECT 2, 'B', 'Value' UNION ALL SELECT 3, 'P', '' UNION ALL SELECT 4, 'C', '' UNION ALL SELECT 5, 'D', '' DECLARE @workgroup VARCHAR(10) = '' SELECT * FROM @Employee E WHERE 1 = (CASE WHEN @workgroup = 'Value' THEN (CASE WHEN E.Status = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.Status In ( 'A','N','LOA','P','D') THEN 1 ELSE 0 END) END) /* Output EmployeeID Status FLDWORKGRP 1 A Value 3 P 5 D */ SET @workgroup = 'Value' SELECT * FROM @Employee E WHERE 1 = (CASE WHEN @workgroup = 'Value' THEN (CASE WHEN E.Status = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.Status In ( 'A','N','LOA','P','D') THEN 1 ELSE 0 END) END) /* Output EmployeeID Status FLDWORKGRP 1 A Value */
Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.
- Edited by Arbi Baghdanian Wednesday, December 3, 2014 7:17 PM Another modification
- Proposed as answer by Jason A Long Wednesday, December 3, 2014 7:21 PM
Wednesday, December 3, 2014 6:57 PM -
@workgroup = 'Value1' Then list all employees that have active 'A' status, other wise list the following status 'A','N','LOA','P','D'
Output
Name Status
EMP1 A
EMP2 A
EMP3 A
@workgroup = 'Value2'
Name Status
EMP1 A
EMP6 N
EMP7 LOA
etc
Wednesday, December 3, 2014 7:07 PM -
FROM EMPLOYEE E LEFT OUTER JOIN DEPT D ON E.DEPT = D.CODE LEFT OUTER JOIN ORGUNIT O ON O.CODE = E.ORGUNIT LEFT OUTER JOIN JOB J ON E.JOB = J.CODE WHERE E.FLDWORKGRP = @workgroup AND E.STATUS = 'A' OR (@workgroup = 'Value' AND E.STATUS IN ('N','LOA','P','D') ORDER BY E.LNAME
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook PageWednesday, December 3, 2014 7:07 PM -
Workgroup has the following values say (Value1, Value2, Value3 etc)
@workgroup = 'Value1' then status = 'A'
for all other @workgroup ('Value2','Value3','.....) status IN ('A','N','LOA','P','D')
I hope this helps and thanks for your time.
Wednesday, December 3, 2014 7:35 PM -
WHERE
1 = (CASE WHEN @workgroup = 'Value1' THEN (CASE WHEN E.Status = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.Status In ( 'A','N','LOA','P','D') THEN 1 ELSE 0 END) END)
Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.
- Edited by Arbi Baghdanian Wednesday, December 3, 2014 7:37 PM
Wednesday, December 3, 2014 7:37 PM -
E.WORKGRP = @workgroup
Sample data
Name Workgroup Status Name1 Value 1 A Name2 Value 2 N Name3 Value 3 D Name4 Value 4 A
Wednesday, December 3, 2014 7:58 PM -
Did you try the latest script that I provide to you? You do NOT need to have "E.WORKGRP = @workgroup " in your WHERE clause.
Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.
Wednesday, December 3, 2014 8:06 PM -
Yes, the query should check E.WORKGRP = @workgroup
I need only those records that has a value for the workgroup.
- Edited by anishap456 Wednesday, December 3, 2014 8:18 PM
Wednesday, December 3, 2014 8:16 PM -
If you cannot precisely describe the logic you need to implement in words, you will likely find it difficult to implement this logic in code. In addition, your readers will be confused about what you said vs. what you mean or intend. So let's back up.
You have a report that has one parameter - @workgroup. So, what values do you intend to supply in that parameter? I notice that you have referred to the literals "Value" and "Value1" while posting pseudo-code that uses "Value 1". Can you see the confusion you are causing?
Next, let's talk about what impact you desire based on the value of the parameter. As best I can tell, when the value of this parameter is <some uncertain but specific value> you want the report to select rows where STATUS = 'A'. Must you also limit the rows based on matching the similarly named column (FLDWORKGRP) to the value of the parameter? And when the value of the parameter is anything but this specific value, what do you want to select? My guess - all rows where status is one of: 'A','N','LOA','P','D'. Does the FLDWORKGRP column need to be considered as well?
As an exercise, I suggest you write a query that is specific to every possible value you intend to supply as a parameter. That will make the logic you need explicit and will allow others to understand what you want to do and to offer suggestions for combining the queries into a single one.
One last comment. CASE is not a control-of-flow construct in tsql. It is an expression that returns a scalar value. That is why you cannot "apply" additional logic in the manner you attempt. And please have a look at the sticky posts at the top of the forum. They provide suggestions regarding how to provide useful information in your posts. A script that can be executed by everyone will greatly reduce confusion and speed up the process of finding a solution.
Wednesday, December 3, 2014 8:52 PM -
Yes,I understood what you are talking about and sorry for the confusion.
Wednesday, December 3, 2014 8:57 PM -
I'm sorry for all the confusion, finally I was able to make it work. In future, I will make sure to describe the logic I want to implement in detail.
WHERE
E.FLDWORKGRP = @workgroup AND
1 = (CASE
WHEN @workgroup = 'UWR' AND E.FLDSTATUS = 'A' THEN 1
ELSE CASE WHEN @workgroup = 'UWMCMS' AND E.FLDSTATUS IN ('A','N','LOA','P','D') THEN 1 ELSE 0 END END)Thanks for all the helpful scripts and suggestions.
- Marked as answer by anishap456 Wednesday, December 3, 2014 9:32 PM
Wednesday, December 3, 2014 9:32 PM