Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: simple case in where clause

    I'm trying to get a case statement to work with 'IN', in the where clause but it doesn't like the syntax.


    WHERE column_1 IN case when@variable = 1 then (2, 3, 4, 5) else (@variable)

    Is this workable?
    Thanks,
    Bill

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    WHERE	(@variable = 1 and column_1 in (2, 3, 4, 5))
    	or column_1 = @variable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    u can't use the case statement in where clause
    use blindman solution r try this to

    if(@variable= 1)
    select * from urtable where column_1 in (1,2,7)
    else
    select * from urtable where column_1 =@variable

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bklr
    u can't use the case statement in where clause
    True. But you can use the CASE expression
    In SQL, it's a Case Expression, *not* a Case Statement
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I've confused myself now.

    Won't your solution cause table/index scans blindman?
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Propose a better one. You could do a variation of bklr's script using a UNION statement instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If performance is an issue here then I'd go with blkr's construct. If not, then I'd settle for a table scan
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    BKLR's would result in alternate execution plans, as opposed to a UNION statement which would be a single execution plan.
    I think UNION is the preferred method, but I also confess to not really caring much between the two.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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