case when in where clause?

• Rob-350472

  SSCertifiable

  Points: 7197

  More actions

  February 10, 2009 at 5:46 am

  #128676

  I have a table of contacts and a table of firms, I am trying to extract contacts at firms. Contacts can be tagged as a specific type - as such at a firm level you will have n contacts of type X, n contacts of type Y etc.

  I wanted to say if there were 3 or more contacts of type X then only extract contacts where their type is like X, otherwise extract contacts where their type is like Y.

  I tried the following, which didn't work:

  SELECT

  c.con_id,

  c.con_name,

  c.con_type,

  f.firm_id,

  f.firm_name

  f.Total_type_A

  FROM contact c

  firm f on f.firm_id = c.firm_id

  WHERE

  CASE WHEN Total_type_A > 3 THEN c.contact_type like '%XX%' ELSE c.contact_type like '%YY%' END

  Any ideas? - I'f researched it and seen dynamic SQL is a common approach to this but that standard SQL can achieve the same result too.

  Thanks!

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  February 10, 2009 at 6:08 am

  #941116

  peitech (2/10/2009)

  ---

  I have a table of contacts and a table of firms, I am trying to extract contacts at firms. Contacts can be tagged as a specific type - as such at a firm level you will have n contacts of type X, n contacts of type Y etc.

  I wanted to say if there were 3 or more contacts of type X then only extract contacts where their type is like X, otherwise extract contacts where their type is like Y.

  I tried the following, which didn't work:

  SELECT

  c.con_id,

  c.con_name,

  c.con_type,

  f.firm_id,

  f.firm_name

  f.Total_type_A

  FROM contact c

  firm f on f.firm_id = c.firm_id

  WHERE

  CASE WHEN Total_type_A > 3 THEN c.contact_type like '%XX%' ELSE c.contact_type like '%YY%' END

  Any ideas? - I'f researched it and seen dynamic SQL is a common approach to this but that standard SQL can achieve the same result too.

  Thanks!

  Use this

  SELECT

  c.con_id,

  c.con_name,

  c.con_type,

  f.firm_id,

  f.firm_name

  f.Total_type_A

  FROM contact c

  firm f on f.firm_id = c.firm_id

  WHERE

  c.contact_type like '%' +

  CASE WHEN Total_type_A > 3 THEN 'XX'

  ELSE 'YY' END + '%'

  kshitij kumar
  kshitij@krayknot.com
  www.krayknot.com

• Rob-350472

  SSCertifiable

  Points: 7197

  More actions

  February 10, 2009 at 6:33 am

  #941135

  Thanks krayknot, that worked perfectly! - Very useful!! 😀