Solved

Using CASE in A WHERE Clause

Posted on 2009-04-05
6
165 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
  • 3
  • 2
6 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 400 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now