If or case staement in a where?

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  February 6, 2013 at 11:34 am

  #288719

  Hi

  I have this in a where :

  AND

  CASE WHEN @InfoKey = 5 THEN

  CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)

  ELSE

  CLIENT_IDENTIFIER_TYPE.Code = @Code

  END

  I tried with an if w/ no luck

  Is it the way I have the syntax?

  Thanks in Advance

  Joe

• sturner

  SSC-Insane

  Points: 22411

  More actions

  February 6, 2013 at 11:52 am

  #1585085

  jbalbo (2/6/2013)

  ---

  Hi

  I have this in a where :

  AND

  CASE WHEN @InfoKey = 5 THEN

  CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)

  ELSE

  CLIENT_IDENTIFIER_TYPE.Code = @Code

  END

  I tried with an if w/ no luck

  Is it the way I have the syntax?

  Thanks in Advance

  Joe

  That should work. or you could say:

  and ((@InfoKey = 5 and CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030) OR

  (@InfoKey != 5 and CLIENT_IDENTIFIER_TYPE.Code = @Code))

  The probability of survival is inversely proportional to the angle of arrival.

• ScottPletcher

  SSC Guru

  Points: 101189

  More actions

  February 6, 2013 at 2:25 pm

  #1585140

  AND

  CASE WHEN @InfoKey = 5 THEN

  CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)

  ELSE

  CLIENT_IDENTIFIER_TYPE.Code = @Code

  END

  The "THEN" and "ELSE" parts of a CASE statement must evaluate to a single value. The value can be specified by an arbitrarily complex expression, but ultimately it must be resolvable to a single value.

  But the expression cannot include any column names, keywords or operators that are not part of an expression to generate a value.

  So, for example, these would be OK:

  THEN cola

  THEN isnull(cola, '') + case when colb = 'a' then 'left' else right' end + colc + cast(cold as varchar(30))

  But this would not:

  ORDER BY CASE WHEN @ord = 'A' THEN ASC ELSE DESC END --ASC/DESC are keywords, not values

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  February 7, 2013 at 7:37 am

  #1585428

  For the record it's a CASE expression not a CASE statement.

  USE tempdb;

  IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT NULL

  DROP TABLE #CLIENT_IDENTIFIER_TYPE;

  GO

  CREATE TABLE #CLIENT_IDENTIFIER_TYPE (Code CHAR(3));

  INSERT INTO #CLIENT_IDENTIFIER_TYPE

  (Code)

  VALUES ('028'),

  ('029'),

  ('030'),

  ('031');

  DECLARE

  @InfoKey INT = NULL,

  --@InfoKey INT = 5,

  @Code CHAR(3) = '031';

  SELECT *

  FROM #CLIENT_IDENTIFIER_TYPE AS CLIENT_IDENTIFIER_TYPE

  WHERE (

  (

  @InfoKey = 5

  AND CLIENT_IDENTIFIER_TYPE.Code IN (028, 029, 030)

  )

  OR (

  ISNULL(@InfoKey, 0) != 5

  AND CLIENT_IDENTIFIER_TYPE.Code = @Code

  )

  );

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 7, 2013 at 8:54 am

  #1585481

  It can be easier to visualise complex filters by switching them into a CROSS APPLY for testing. Shamelessly nicking Orlando's setup, it might look like this;

  USE tempdb;

  IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT NULL

  DROP TABLE #CLIENT_IDENTIFIER_TYPE;

  GO

  CREATE TABLE #CLIENT_IDENTIFIER_TYPE (Code CHAR(3));

  INSERT INTO #CLIENT_IDENTIFIER_TYPE

  (Code)

  VALUES ('028'),

  ('029'),

  ('030'),

  ('031');

  DECLARE

  @InfoKey INT = NULL,

  --@InfoKey INT = 5,

  @Code CHAR(3) = '031';

  SELECT

  *,

  x.SimpleFilter

  FROM #CLIENT_IDENTIFIER_TYPE AS CLIENT_IDENTIFIER_TYPE

  CROSS APPLY (

  SELECT SimpleFilter = CASE

  WHEN @InfoKey = 5 AND CLIENT_IDENTIFIER_TYPE.Code IN (028, 029, 030) THEN 1

  WHEN (@InfoKey <> 5 OR @InfoKey IS NULL) AND CLIENT_IDENTIFIER_TYPE.Code = @Code THEN 2

  ELSE NULL END

  ) x

  WHERE x.SimpleFilter IS NOT NULL

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  February 7, 2013 at 9:30 am

  #1585526

  ChrisM@Work (2/7/2013)

  ---

  It can be easier to visualise complex filters by switching them into a CROSS APPLY for testing. Shamelessly nicking Orlando's setup, it might look like this;

  USE tempdb;

  IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT NULL

  DROP TABLE #CLIENT_IDENTIFIER_TYPE;

  GO

  CREATE TABLE #CLIENT_IDENTIFIER_TYPE (Code CHAR(3));

  INSERT INTO #CLIENT_IDENTIFIER_TYPE

  (Code)

  VALUES ('028'),

  ('029'),

  ('030'),

  ('031');

  DECLARE

  @InfoKey INT = NULL,

  --@InfoKey INT = 5,

  @Code CHAR(3) = '031';

  SELECT

  *,

  x.SimpleFilter

  FROM #CLIENT_IDENTIFIER_TYPE AS CLIENT_IDENTIFIER_TYPE

  CROSS APPLY (

  SELECT SimpleFilter = CASE

  WHEN @InfoKey = 5 AND CLIENT_IDENTIFIER_TYPE.Code IN (028, 029, 030) THEN 1

  WHEN (@InfoKey <> 5 OR @InfoKey IS NULL) AND CLIENT_IDENTIFIER_TYPE.Code = @Code THEN 2

  ELSE NULL END

  ) x

  WHERE x.SimpleFilter IS NOT NULL

  Another great use of APPLY. If you were shameless you would have ripped it off without saying mych, like I did to sturner's example

  Just noticed I should have nicked up some single quotes on the IN too in order to avoid the implicit data type conversion to INT:

  WHEN @InfoKey = 5 AND CLIENT_IDENTIFIER_TYPE.Code IN ('028', '029', '030') THEN 1

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato