Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 30th, 2005, 03: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"
Reply With Quote
  #2 (permalink)  
Old December 1st, 2005, 11:47 AM
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.

Reply With Quote
  #3 (permalink)  
Old December 1st, 2005, 01: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
Reply With Quote
  #4 (permalink)  
Old December 2nd, 2005, 09: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....

Reply With Quote
  #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))
Reply With Quote
The Following User Says Thank You to gfcb5624 For This Useful Post:
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 09: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 11:45 AM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 04:49 AM



All times are GMT -4. The time now is 09:18 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.