how to use "CASE" in "WHERE" clause?

• ThanhThanh

  Grasshopper

  Points: 19

  More actions

  September 3, 2004 at 12:59 am

  #59315

  Hi All,

  Do you know how to use "CASE" in "WHERE" clause? Could you please give me some examples! Thanks in advance!

  I need to write a query like this

  SELECT table1.field1, table1.field1, table2.field2

  FROM table1 , table2 

  WHERE table1.field1 *= table2.field1

  AND table2.field2 = 2004 (this condition only when table2.field2 is not null )

  (this is just a small part of a complicated query inside a complicated store procedure)

  Regards!

  ---

  Kindest Regards,

  Learning

• Nivedita Sundar.N

  Mr or Mrs. 500

  Points: 566

  More actions

  September 3, 2004 at 4:05 am

  #521613

  Hi,

    Please check the sql statement given below.

  SELECT table1.field1, table1.field1, table2.field2

  FROM table1 , table2 

  WHERE table1.field1 *= table2.field1

  AND table2.field2 = (case when table2.field2 is null then null else 2004 end)

  Hope this helps.

  Nivedita Sundar.N

   

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  September 3, 2004 at 5:21 am

  #521617

  Not very complicated but hopefully will help you:

  SELECT *

    FROM pubs.dbo.authors            AU

      LEFT JOIN pubs.dbo.titleauthor TA

        ON AU.au_id = CASE WHEN ISNULL(TA.au_id, '') = ''  THEN NULL ELSE TA.au_id END

      LEFT JOIN pubs.dbo.titles      TT

        ON TA.title_id = CASE WHEN ISNULL(TA.au_id, '') = ''  THEN NULL

                              WHEN ISNULL(TA.au_id, '') <> '' THEN TA.title_id END

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Luigi Visintin

  SSC Eights!

  Points: 822

  More actions

  September 5, 2004 at 7:11 am

  #521783

  Why not try this:

  SELECT table1.field1, table1.field1, table2.field2

  FROM table1 , table2 

  WHERE table1.field1 *= table2.field1

  AND (CASE WHEN table2.field2 is null THEN (1=1) ELSE (table2.field2 = 2004)END)

  so the CASE will return either TRUE (if f2 is null or TRUE/FALSE depending on f2 comparison with 2004...

  HTH

  Luigi