?
Solved

Using CASE in A WHERE Clause

Posted on 2009-04-05
6
Medium Priority
?
194 Views
Last Modified: 2012-05-06
I am trying to use CASE in a WHERE clause but NO LUCK as I get incorrect syntax?

I want to return results where Start is > Getdate - 1t minutes

On the question preview I cannot see the plus signs so it should be like this

'tblFileList.ListDate (plus sign) ColumnData'
SELECT
	tblOrder.CaseID,
	tblOrder.OrderID,
	tblOrderTask.Alert,
	tblOrderTask.TaskID,
	CASE
		WHEN AlertDurationID = 0 THEN tblFileList.ListDate + ColumnData
		WHEN AlertDurationID = 1 THEN DATEADD(n, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 2 THEN DATEADD(hh, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 3 THEN DATEADD(dd, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 4 THEN DATEADD(ww, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 5 THEN DATEADD(mm, AlertStart, tblFileList.ListDate + ColumnData)
	END As Start
FROM
	tblOrder
INNER JOIN
	tblOrderTask
ON
	tblOrder.OrderID = tblOrderTask.OrderID
INNER JOIN
	tblFileListColumn
INNER JOIN
	tblFileList
ON
	tblFileListColumn.FileListID = tblFileList.FileListID
ON
	tblOrder.CaseID = tblFileList.CaseId
INNER JOIN
	tblDuration
ON
	tblOrderTask.AlertDurationID = tblDuration.DurationID
WHERE
	(tblOrderTask.Alert = 1)
AND
	(tblFileListColumn.ColumnTypeID = 1)
AND
	(tblOrderTask.TaskStatusID > 2)
AND
	CASE
		WHEN AlertDurationID = 0 THEN (tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
		WHEN AlertDurationID = 1 THEN DATEADD(n, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
		WHEN AlertDurationID = 2 THEN DATEADD(hh, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
		WHEN AlertDurationID = 3 THEN DATEADD(dd, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
		WHEN AlertDurationID = 4 THEN DATEADD(ww, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
		WHEN AlertDurationID = 5 THEN DATEADD(mm, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
	END

Open in new window

0
Comment
Question by:MMTadmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 1600 total points
ID: 24073672
Hi MMTadmin,

The comparison in the where cannot be inside the CASE, but the data you wish to use in the comparison can be returned by the CASE ... try

lwadwell
AND
      CASE
            WHEN AlertDurationID = 0 THEN (tblFileList.ListDate + ColumnData)  
            WHEN AlertDurationID = 1 THEN DATEADD(n, AlertStart, tblFileList.ListDate + ColumnData) 
            WHEN AlertDurationID = 2 THEN DATEADD(hh, AlertStart, tblFileList.ListDate + ColumnData) 
            WHEN AlertDurationID = 3 THEN DATEADD(dd, AlertStart, tblFileList.ListDate + ColumnData) 
            WHEN AlertDurationID = 4 THEN DATEADD(ww, AlertStart, tblFileList.ListDate + ColumnData) 
            WHEN AlertDurationID = 5 THEN DATEADD(mm, AlertStart, tblFileList.ListDate + ColumnData) 
      END > DATEADD(n, -15,GETDATE())

Open in new window

0
 
LVL 2

Author Comment

by:MMTadmin
ID: 24073699
Line 13: Incorrect syntax near '>'. same as I was getting before
0
 
LVL 2

Accepted Solution

by:
MMTadmin earned 0 total points
ID: 24073711
You were correct - thanks

WHERE
	(tblOrderTask.Alert = 1)
AND
	(tblFileListColumn.ColumnTypeID = 1)
AND
	(tblOrderTask.TaskStatusID > 2)
AND
	CASE
		WHEN AlertDurationID = 0 THEN tblFileList.ListDate + ColumnData
		WHEN AlertDurationID = 1 THEN DATEADD(n, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 2 THEN DATEADD(hh, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 3 THEN DATEADD(dd, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 4 THEN DATEADD(ww, AlertStart, tblFileList.ListDate + ColumnData)
		WHEN AlertDurationID = 5 THEN DATEADD(mm, AlertStart, tblFileList.ListDate + ColumnData)
	END < DATEADD(n, -15, GETDATE())

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 24073727
Try it this way:
WHERE	tblOrderTask.Alert = 1
		AND tblFileListColumn.ColumnTypeID = 1
		AND tblOrderTask.TaskStatusID > 2
		AND	(
			AlertDurationID = 0 And (tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
			Or AlertDurationID = 1 And DATEADD(n, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
			Or AlertDurationID = 2 And DATEADD(hh, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
			Or AlertDurationID = 3 And DATEADD(dd, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
			Or AlertDurationID = 4 And DATEADD(ww, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
			Or AlertDurationID = 5 And DATEADD(mm, AlertStart, tblFileList.ListDate + ColumnData) > DATEADD(n, -15,GETDATE())
			)

Open in new window

0
 
LVL 2

Author Comment

by:MMTadmin
ID: 24073754
Sorry I mucked up everything the web site went crazy on me  ;-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24073791
>>Response was good - to acperkins, thanks for answering the question also I appreciate it, when I get enough data I will try that .. but I am wondering how that even works,an OR instead of a case ... weird ;-)<<
Actually it is quite the opposite.  There is very rarely a need to use a CASE statement in a WHERE clause and in general it should be avoided for exactly the reason you have discovered:  It is a PITA to debug.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question