locked
Case statement in where clause RRS feed

  • 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 Page

    Wednesday, 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.


    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