View Single Post
  #3 (permalink)  
Old December 1st, 2005, 01:30 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

It's also important to understand that in SQL, CASE is an expression and not an executable "control-of-flow" statement. Thus, the CASE expression, when evaluated, just returns a value which can then be tested or otherwise combined with other expressions (in statements). You cannot use the result of a CASE expression directly as the predicate of a WHERE clause. Thus:
Code:
SELECT * FROM sometable
WHERE CASE WHEN somecondition THEN 1
            WHEN someothercondition THEN 2
            ELSE ... END
is not legal, because the WHERE clause would be the equivalent of
Code:
WHERE 1

 or 

WHERE 2
which is not legal syntax. Instead, the code could read:
Code:
SELECT * FROM sometable
WHERE 1 = CASE WHEN somecondition THEN 1 
                WHEN someothercondition THEN 2
                ELSE ... END
and that's legal.

But robprell is quite correct in that you'd probably be better off just using logical expressions rather than CASE.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote