Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: 'Case' statement inside 'Where' clause

    Hi

    I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following?

    DECLARE @searchCriteria Int
    SET @searchCriteria = 2

    SELECT column1, column2 FROM TABLE WHERE
    CASE @searchCriteria
    WHEN 1 THEN (column3 = 1000100)
    WHEN 2 THEN (column3 = 1000101)
    END CASE



    ...cheers

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh - reminded me of a blog post:
    http://weblogs.sqlteam.com/jeffs/arc.../03/60195.aspx

    100% applicable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    ....ah....i do come from a VB background as well so thats my excuse lol!

    Good article, makes sense - is there a way round what i'm trying to do above?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - based on Jeff's idea that you should simply treat CASE just like a function - what do you come up with? How would you put together a wherre clause using any other function?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aw, pootle, can't you just tell him? he doesn't appear to look like a student looking for a homework answer...

    change this --

    SELECT column1, column2 FROM TABLE WHERE
    CASE @searchCriteria
    WHEN 1 THEN (column3 = 1000100)
    WHEN 2 THEN (column3 = 1000101)
    END CASE

    to this --

    SELECT column1, column2 FROM TABLE WHERE
    column3 = CASE @searchCriteria
    WHEN 1 THEN 1000100
    WHEN 2 THEN 1000101
    ELSE NULL
    END CASE

    okay, mattock, notice anything unusual about this? something i happened to slip in there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Of course I can. And I know mattock isn't a studey - he's posted here a few times. BUT - it don't half stick better if you confiddle it fer yerself rather than copy and paste someone elses code!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    ahh yeh - from the article you basically can't put in any T-SQL - only 'stuff' you can return like strings - makes perfect sense.

    Have tried the select statement you've put but keep getting a syntax error near the keyword case!

    heres exactly what i'm trying:

    DECLARE @searchCriteria Int
    SET @searchCriteria = 2

    SELECT * FROM VENUE WHERE
    venue_id = CASE @searchCriteria
    WHEN 1 THEN 1000100
    WHEN 2 THEN 1000101
    ELSE NULL
    END CASE

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first CASE is Ok, but the second case (as in END CASE) will byte you.

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rats, i hate replicating an error like that -- i didn't even notice

    so, mattock, do you understand what happens when the CASE expression gives venue_id = NULL? shouldn't it be IS NULL??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I kind of wondered about that myself, but I was willing to wait to see if there were complaints about "nothing comes back" when the search argument wasn't a 1 or a 2, or if that is what they really wanted.

    Note that the IS NULL throws a willy into things for syntactical reasons.

    -PatP

  11. #11
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Quote Originally Posted by Pat Phelan
    The first CASE is Ok, but the second case (as in END CASE) will byte you.

    -PatP
    i've got rid of the second case and it works....

    SELECT * FROM VENUE WHERE
    venue_id = CASE @searchCriteria
    WHEN 1 THEN 1000100
    WHEN 2 THEN 1000101
    ELSE NULL
    END


  12. #12
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Quote Originally Posted by r937
    rats, i hate replicating an error like that -- i didn't even notice

    so, mattock, do you understand what happens when the CASE expression gives venue_id = NULL? shouldn't it be IS NULL??
    mmm, that doesn't work - throws an error, if i put 'IS NULL' then it would be like putting "venue_id = IS NULL"

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mattock, i am just messin' wit ya

    what do you want to happen when @searchCriteria is neither 1 nor 2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    when it's not 1 or 2 i would need it to ignore that statement altogether if thats possible as i presume as it stands (NULL) it will find all venue_id's with no value?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ignore which statement??? there you go again with the "statements"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •