none
T-SQL case statement in where clause

    Question


  • I have two tables and join them on costcenter.  I get all the employees in the cost center assigned to them.

    I am trying to restrict or filter by PAS_GROUP of the employee who is logged in. 
    'MGR' (Managers) should not have access to 'MGR'(Other Managers)  AND 'DIR' (Directors) AND 'EXC'(Executives). 

    The code below works for the 'MGR' but not sure how I can make it work for Directors and Executives. Need some help....

    This code works:
    1 SELECT     Reporting.CostCenter,    
    2                  Employee_1.EmpNumber,   
    3                  Employee_1.PAS_GROUP,   
    4                  Employee_1.PAS_ACCESS,  
    5                  Employee_1.LastName,   
    6                  Employee_1.FirstName,  
    7                  Employee_1.EmpNumber,  
    8                    
    9                  (SELECT     PAS_GROUP  
    10                     FROM       Employee  
    11                     WHERE      (EmpNumber = @empid))             AS MGRGROUP  
    12  
    13 FROM         Employee AS Employee_1   
    14  
    15 INNER JOIN  
    16                   Reporting ON Employee_1.CostCenter = Reporting.CostCenter  
    17  
    18 WHERE       Reporting.Emp ID = @empid AND   
    19                    Employee_1.EmpNumber <> @empid AND   
    20
    21                    Employee_1.PAS_GROUP <> N'MGR'  AND   
    22                    Employee_1.PAS_GROUP <> N'DIR'   AND   
    23                    Employee_1.PAS_GROUP <> N'EXC'  
    24  
    25  


    Need some help ...

    THIS does not work.  I think I need to have a CASE Statement in the WHERE clause.
    Sample:
     
    1 SELECT     Reporting.CostCenter,          
    2                   Employee_1.EmpNumber,         
    3                   Employee_1.PAS_GROUP,         
    4                   Employee_1.PAS_ACCESS,        
    5                   Employee_1.LastName,         
    6                   Employee_1.FirstName,        
    7                   Employee_1.EmpNumber,        
    8                           
    9                   (SELECT     PAS_GROUP        
    10                      FROM       Employee        
    11                      WHERE      (EmpNumber = @empid))             AS MGRGROUP        
    12         
    13  FROM         Employee AS Employee_1         
    14         
    15  INNER JOIN        
    16                    Reporting ON Employee_1.CostCenter = Reporting.CostCenter        
    17         
    18  WHERE       Reporting.Emp ID = @empid AND         
    19                     Employee_1.EmpNumber <> @empid AND         
    20     
    21 (     
    22 CASE  MGRGROUP         
    23 WHEN 'MGR' THEN           
    24                              
    25                     Employee_1.PAS_GROUP <> N'MGR'  AND         
    26                     Employee_1.PAS_GROUP <> N'DIR'   AND         
    27                     Employee_1.PAS_GROUP <> N'EXC'        
    28         
    29         
    30  WHEN 'DIR' THEN             
    31                     Employee_1.PAS_GROUP <> N'DIR'   AND         
    32                     Employee_1.PAS_GROUP <> N'EXC'        
    33  END        
    34  )        
    35  


    web developer
    Wednesday, February 25, 2009 3:25 PM

Answers

  • Ah... I must have missed that... try

    SELECT Reporting.CostCenter,                
            Employee_1.EmpNumber,               
            Employee_1.PAS_GROUP,               
            Employee_1.PAS_ACCESS,              
            Employee_1.LastName,               
            Employee_1.FirstName,              
            Employee_1.EmpNumber,  
            MGRGROUP.PAS_GROUP     
          
    FROM Employee AS Employee_1               
                   
    INNER JOIN Reporting   
    ON Employee_1.CostCenter = Reporting.CostCenter              
                   
    inner join (SELECT EmpNumber,   
               PAS_GROUP              
                FROM Employee) MGRGROUP  
    on  MGRGROUP.EmpNumber = @empid         
     
    WHERE Reporting.[Emp ID] = @empid   
    AND   Employee_1.EmpNumber <> @empid      
    AND (     
    MGRGROUP.PAS_GROUP = 'MGR' and Employee_1.PAS_GROUP not in ('MGR','DIR','EXC')     
    or               
    MGRGROUP.PAS_GROUP = 'DIR' and Employee_1.PAS_GROUP not in ('DIR','EXC')     
    )  
     
     
     

    It parses, but i haven't tested it...

    If you can supply table definitions and some sample data in the form of inserts, it makes it a lot easier for us to help devise a query which meets your requirement.

    Regards

     
    Jon Royales
    • Marked as answer by vj78 Wednesday, February 25, 2009 5:54 PM
    Wednesday, February 25, 2009 5:31 PM

All replies

  • You shouldn't need a case statement, try this...

     SELECT     Reporting.CostCenter,             
                       Employee_1.EmpNumber,            
                       Employee_1.PAS_GROUP,            
                       Employee_1.PAS_ACCESS,           
                       Employee_1.LastName,            
                       Employee_1.FirstName,           
                       Employee_1.EmpNumber,           
                                  
                      (SELECT     PAS_GROUP           
                          FROM       Employee           
                          WHERE      (EmpNumber = @empid))             AS MGRGROUP           
                
      FROM         Employee AS Employee_1            
                
      INNER JOIN           
                       Reporting ON Employee_1.CostCenter = Reporting.CostCenter           
                
      WHERE       Reporting.[Emp ID] = @empid AND            
                         Employee_1.EmpNumber <> @empid   
     
    AND   
    (  
    MGRGROUP = 'MGR' and Employee_1.PAS_GROUP not in ('MGR','DIR','EXC')  
    or            
    MGRGROUP = 'DIR' and Employee_1.PAS_GROUP not in ('DIR','EXC')   
     )    

    Jon Royales
    Wednesday, February 25, 2009 4:23 PM
  • it gives me a error message

    Error message: Invalid Column Name 'MGRGROUP'


    web developer
    Wednesday, February 25, 2009 5:13 PM
  • What if you try something like this?

    SELECT     Reporting.CostCenter,                
               Employee_1.EmpNumber,               
               Employee_1.PAS_GROUP,               
               Employee_1.PAS_ACCESS,              
               Employee_1.LastName,               
               Employee_1.FirstName,              
               Employee_1.EmpNumber  
      FROM         Employee AS Employee_1               
        INNER JOIN Reporting ON Employee_1.CostCenter = Reporting.CostCenter              
        JOIN (SELECT     PAS_GROUP AS MGRGROUP        
              FROM       Employee              
              WHERE      (EmpNumber = @empid)) AS MGRGROUPTBL on 1=1  
      WHERE  Reporting.[Emp ID] = @empid AND               
             Employee_1.EmpNumber <> @empid      
        AND (   MGRGROUPTBL.MGRGROUP = 'MGR' and Employee_1.PAS_GROUP not in ('MGR','DIR','EXC')     
             or MGRGROUPTBL.MGRGROUP = 'DIR' and Employee_1.PAS_GROUP not in ('DIR','EXC'))       
     

    I hope this helps, Rafael Medeiros
    Wednesday, February 25, 2009 5:21 PM
  • Ah... I must have missed that... try

    SELECT Reporting.CostCenter,                
            Employee_1.EmpNumber,               
            Employee_1.PAS_GROUP,               
            Employee_1.PAS_ACCESS,              
            Employee_1.LastName,               
            Employee_1.FirstName,              
            Employee_1.EmpNumber,  
            MGRGROUP.PAS_GROUP     
          
    FROM Employee AS Employee_1               
                   
    INNER JOIN Reporting   
    ON Employee_1.CostCenter = Reporting.CostCenter              
                   
    inner join (SELECT EmpNumber,   
               PAS_GROUP              
                FROM Employee) MGRGROUP  
    on  MGRGROUP.EmpNumber = @empid         
     
    WHERE Reporting.[Emp ID] = @empid   
    AND   Employee_1.EmpNumber <> @empid      
    AND (     
    MGRGROUP.PAS_GROUP = 'MGR' and Employee_1.PAS_GROUP not in ('MGR','DIR','EXC')     
    or               
    MGRGROUP.PAS_GROUP = 'DIR' and Employee_1.PAS_GROUP not in ('DIR','EXC')     
    )  
     
     
     

    It parses, but i haven't tested it...

    If you can supply table definitions and some sample data in the form of inserts, it makes it a lot easier for us to help devise a query which meets your requirement.

    Regards

     
    Jon Royales
    • Marked as answer by vj78 Wednesday, February 25, 2009 5:54 PM
    Wednesday, February 25, 2009 5:31 PM
  • Thank you!

    It works :)

    I will make sure that I supply table definitions and data next time there is a problem.

    Regards,
    web developer
    Wednesday, February 25, 2009 5:56 PM