Professional Web Applications Themes

CASE STATEMENTS - Microsoft SQL / MS SQL Server

hey guys, is it possible to use case statement in WHERE CLAUSE ? i m trying to do some thing like this but it gives me error SELECT * FROM TABLE1 WHERE TABLE1.ID IS NOT NULL AND (CASE WHEN TABLE1.ID = 0 THEN TABLE1.ACTIVE = 1 END) any help will be great. Thanks, Brij...

  1. #1

    Default CASE STATEMENTS

    hey guys,

    is it possible to use case statement in WHERE CLAUSE ?
    i m trying to do some thing like this but it gives me error

    SELECT * FROM TABLE1 WHERE TABLE1.ID IS NOT NULL AND (CASE
    WHEN TABLE1.ID = 0 THEN
    TABLE1.ACTIVE = 1
    END)

    any help will be great.

    Thanks,

    Brij


    Brij Guest

  2. #2

    Default Re: CASE STATEMENTS

    SELECT *
    FROM TABLE1
    WHERE TABLE1.ID IS NOT NULL
    AND CASE WHEN TABLE1.ID = 0 THEN TABLE1.ACTIVE = 1 END

    Don't forget that CASE is an expression and you can't initialise cols/vars
    in the THEN or ELSE clause, you would need to write...

    AND TABLE1.ACTIVE = CASE WHEN TABLE1.ID = 0 THEN 1
    ELSE NULL
    END

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  3. #3

    Default Re: CASE STATEMENTS

    im not sure what is your exact requirement but you can try this.
    (untested)
    SELECT * FROM TABLE1 WHERE TABLE1.ID IS NOT NULL AND table1.active = (case
    table1.id when 0 then 1 else table1.active end)

    --
    -Vishal
    "Brij Singh" <com> wrote in message
    news:3f140b51$0$24525$newshosting.com... 


    Vishal Guest

  4. #4

    Default Re: CASE STATEMENTS

    My only concern here is that doing

    AND TABLE1.ACTIVE = CASE WHEN TABLE1.ID = 0 THEN 1
    ELSE NULL
    END


    will try to find TABLE1.ACTIVE = NULL if TABLE1.ID != 0

    Not sure this is what you want since all data would be eliminated.

    I think maybe you are want to do this.

    AND TABLE1.ACTIVE = CASE WHEN TABLE1.ID = 0 THEN 1
    ELSE TABLE1.ACTIVE
    END

    Since I think you are after anything where TABLE1.ID != 0 where
    TABLE1.ACTIVE = anything.

    "Tony Rogerson" <eu.com> wrote in message
    news:eY$phx.gbl... 


    James Guest

Similar Threads

  1. Is there a way to convert lower case text to upper case text in PHP?
    By tanas@ing.com.au in forum PHP Development
    Replies: 3
    Last Post: December 11th, 06:12 AM
  2. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  3. Star in case statements
    By Hal E. Fulton in forum Ruby
    Replies: 2
    Last Post: August 21st, 06:57 AM
  4. If/else statements - help.
    By Chris in forum PHP Development
    Replies: 1
    Last Post: June 28th, 01:55 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139