Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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
 
Old January 31st, 2012, 09:25 PM
Registered User
 
Join Date: Jan 2012
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Red face 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)
 
Old February 1st, 2012, 06:47 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi,

check out this previous thread to see why you cannot use a Case statement like that in the where clause:
Case Statement In Where Clause Logic

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
The Following User Says Thank You to disel2010 For This Useful Post:
Beginner_2012 (February 2nd, 2012)
 
Old February 2nd, 2012, 09:19 PM
Registered User
 
Join Date: Jan 2012
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Yes, it works. Thanks so much.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Case Statement eastwest SQL Server 2005 8 August 6th, 2009 05:19 PM
Case statement cole SQL Language 3 May 8th, 2005 03:02 PM
case statement Hudson40 Access VBA 1 February 11th, 2005 11:31 AM
Using A CASE Statement fastcorvette Access 5 December 24th, 2003 01:39 PM
case statement jakeone Beginning PHP 10 August 19th, 2003 03:03 PM





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