Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 30th, 2005, 04:38 PM
Registered User
 
Join Date: Nov 2005
Location: York Haven, PA, .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Case Statement In Where Clause Logic

I know this is possible, but I can't seem to get the syntax correct. Any help would be much appreciated.


Where bi.EDIID = 5444 AND (
                 (bid.ssn IS NULL and el.PersonId is NOT NULL)
            or
                    (el.personid is NULL and bid.ssn is NOT NULL)
            or
CASE WHEN (bid.SSN IS NOT NULL AND el.personid IS NOT NULL)
    THEN ((el.PremiumDue <> bid.PremiumDue)
     or (el.PremiumDueRound <> bid.PremiumDueRounded))
ELSE (1 = 2)
END


http://www.jtechonline.com"
  #2 (permalink)  
Old December 1st, 2005, 12:47 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry this does not make sense to me. Why do you want to use a case statement in a where clause. Why not just use the same type of where logic you were using and eliminate the case entirely. If you stick to "and" & "or" logic entirely (no case) your live will be much happier. There is nothing in your case you can't do with and/or.

  #3 (permalink)  
Old December 1st, 2005, 02:30 PM
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
  #4 (permalink)  
Old December 2nd, 2005, 10:10 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well stated (like a lawyer). Main point is there does not appear to be any advantage in using a case in your where even if you can do it. Unless someone can point one out to me....

  #5 (permalink)  
Old October 27th, 2011, 03:14 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2011
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
Default

here is what I use and it works


Code:
Declare @IDProvider int = 123


Select * from TBL_Iso_Summary
where period_Month=9
and period_year=2011
and        (ID_Provider_Primary = @IDProvider OR (@IDProvider IS NULL OR @IDProvider = 0))
The Following User Says Thank You to gfcb5624 For This Useful Post:


Similar Threads
Thread Thread Starter Forum Replies Last Post
Case in Where Clause acko SQL Server 2000 12 September 12th, 2011 01:43 PM
CASE WHEN WHERE CLAUSE deontae45 SQL Server 2000 2 January 11th, 2011 10:03 AM
Problems using case statement in where clause vghiya SQL Server 2000 3 May 28th, 2007 05:12 AM
Case in Where Clause demiwolf SQL Server 2000 3 March 23rd, 2006 12:45 PM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 05:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.