CASE in WHERE Clause

• gaurav-404321

  SSCommitted

  Points: 1593

  More actions

  September 10, 2009 at 3:44 am

  #135628

  I need to put a CASE condition in WHERE clause

  Following is the table containing records in following manner:

  name leavetypeid gender

  A 1 M

  A 2 M

  A 3 M

  B 1 F

  B 2 F

  B 3 F

  i need a quey like:

  1. If gender='M' then show records of leavetypeid in (1,2,3)

  2. If gender='F' then show records of leavetypeid in (1,2)

  select a.gender,a.name from

  a

  WHERE a.leavetypeid in (CASE WHEN a.GENDER='F' THEN 1 END)

  I am writing the query as above but here it is allowing me only select 1 type of leavetypeid i.e. 1

  i need multiple like

  IF GENDER='M' THEN LeaveTypeID in (1,2,3)

  IF GENDER='F' THEN LeaveTypeID in (1,2)

  Please help in resolving the problem

• matt6288

  SSCertifiable

  Points: 5058

  More actions

  September 10, 2009 at 6:05 am

  #1051675

  There is probably a better way to do this but here is my first shot.

  CREATE TABLE #temp (NAME VARCHAR(10), leavetypeid INT, gender CHAR(1))

  INSERT INTO #temp

  SELECT 'A',1,'M'

  UNION ALL

  SELECT 'A',2,'M'

  UNION ALL

  SELECT 'A',3,'M'

  UNION ALL

  SELECT 'B',1,'F'

  UNION ALL

  SELECT 'B',2,'F'

  UNION ALL

  SELECT 'B',3,'F'

  SELECT a.gender, a.NAME FROM #temp a

  WHERE a.gender = CASE WHEN a.gender = 'M' AND a.leavetypeid IN (1,2,3) THEN 'M'

  when a.gender = 'F' and a.leavetypeid in (1,2) then 'F' END

  DROP TABLE #temp

• Alex M-728960

  SSC Eights!

  Points: 970

  More actions

  September 10, 2009 at 10:16 am

  #1051817

  Why using a CASE in the first place? The following will get the same result:

  SELECT a.gender, a.NAME FROM #temp a

  WHERE (a.gender = 'M' AND a.leavetypeid IN (1,2,3))

  or (a.gender = 'F' AND a.leavetypeid in (1,2))