p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   How to use case in where statement (http://p2p.wrox.com/showthread.php?t=86515)

Beginner_2012 January 31st, 2012 09:25 PM

How to use case in where statement
 
Can someone help me to get this query to work?

DECLARE @option bit
,@begindate datetime
,@enddate datetime
/@paymentdate datetime

Set @option = 1
,@begindate = ‘2/1/2012’
,@enddate = ‘2/20/2012’
,@paymentdate = '2/8/2012'

Select * from inventory i
where i.TransactionType = '810'
and (CASE @option
WHEN 1 THEN i.PaymentDueDate between @begindate and @enddate
ELSE i.CreateDate between @begindate and @enddate
END)

disel2010 February 1st, 2012 06:47 AM

Hi,

check out this previous thread to see why you cannot use a Case statement like that in the where clause:
http://p2p.wrox.com/sql-server-2000/...use-logic.html

I would suggest to rewrite your sql as follows:
Code:


Select * from inventory i
where
 i.TransactionType = '810'
 and
 (
  ((@option = 1) and (i.PaymentDueDate between @begindate and enddate))
  or
  ((@option <> 1) and (i.CreateDate between @begindate and @enddate))
 )

Hope this helps

Beginner_2012 February 2nd, 2012 09:19 PM

Yes, it works. Thanks so much.


All times are GMT -4. The time now is 03:19 AM.

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