• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Using CASE in A WHERE Clause

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
MMTadmin
Asked:
MMTadmin
  • 3
  • 2
3 Solutions
 
lwadwellCommented:
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
 
MMTadminAuthor Commented:
Line 13: Incorrect syntax near '>'. same as I was getting before
0
 
MMTadminAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
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
 
MMTadminAuthor Commented:
Sorry I mucked up everything the web site went crazy on me  ;-)
0
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now