MMTadmin
asked on
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'
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry I mucked up everything the web site went crazy on me ;-)
>>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.
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.
ASKER