Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

CASE IN WHERE CLAUSE IN SQL

Posted on 2014-03-17
4
Medium Priority
?
234 Views
Last Modified: 2014-04-01
Hello,
I have the below query:
 SELECT "Facilities"."FacName", "NursingStations"."NsName", "Facilities"."FacID", "KeyIdentifiers"."DeaClass", "vw_crystal_ReorderDirectionsExpandedSig"."ExpandedSig", "Reorders"."CutoffDt", "Reorders"."LastRxNo", "Reorders"."LastFillDt", "Reorders"."Qty", "Reorders"."PhName", "Patients"."PatLName", "Patients"."PatFName", "Reorders"."TotalQtyWritten", "Reorders"."TotalQtyDispensed", "Reorders"."DateWritten", "Reorders"."DrugLabelName", "Reorders"."ProfileOnly"
 FROM   (((("FwReports"."dbo"."Facilities" "Facilities" INNER JOIN "FwReports"."dbo"."NursingStations" "NursingStations" ON "Facilities"."FacID"="NursingStations"."FacID") INNER JOIN "FwReports"."dbo"."Patients" "Patients" ON ("NursingStations"."FacID"="Patients"."FacID") AND ("NursingStations"."NsID"="Patients"."NsID")) INNER JOIN "FwReports"."dbo"."Reorders" "Reorders" ON ("Patients"."FacID"="Reorders"."FacID") AND ("Patients"."PatID"="Reorders"."PatID")) LEFT OUTER JOIN "FwReports"."dbo"."vw_crystal_ReorderDirectionsExpandedSig" "vw_crystal_ReorderDirectionsExpandedSig" ON (("Reorders"."FacID"="vw_crystal_ReorderDirectionsExpandedSig"."FacID") AND ("Reorders"."RoNo"="vw_crystal_ReorderDirectionsExpandedSig"."RoNo")) AND ("Reorders"."PatID"="vw_crystal_ReorderDirectionsExpandedSig"."PatID")) LEFT OUTER JOIN "FwReports"."dbo"."KeyIdentifiers" "KeyIdentifiers" ON "Reorders"."NDC"="KeyIdentifiers"."NDC"
 WHERE  ("KeyIdentifiers"."DeaClass"='C-II' OR "KeyIdentifiers"."DeaClass"='C-III' OR "KeyIdentifiers"."DeaClass"='C-IV' 
 OR "KeyIdentifiers"."DeaClass"='C-V') AND "Facilities"."FacID"='88' AND 
 ("Reorders"."CutoffDt" IS  NULL  OR "Reorders"."CutoffDt">={ts '2014-03-17 11:19:48'})
  AND "Reorders"."ProfileOnly"=0 AND ("Reorders"."LastFillDt">={ts '2014-03-10 00:00:00'} 
  AND "Reorders"."LastFillDt"<{ts '2014-03-17 00:00:01'})
  and 
  case 
  when KeyIdentifiers.DeaClass='C-II'   THEN DATEDIFF(DAY,GETDATE(),REORDERS.DateWritten) > 60
  END

Open in new window

When I execute this it says incorrect syntax near '>'
Not sure why.Please help.
0
Comment
Question by:Star79
4 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 39934748
Hi Star,

CASE returns a value, not an expression or clause.  :(  But it's easy to fix.

and
  case
  when KeyIdentifiers.DeaClass='C-II'   THEN DATEDIFF(DAY,GETDATE(),REORDERS.DateWritten) > 60
  END

becomes:

and ((KeyIdentifiers.DeaClass='C-II' and DATEDIFF(DAY,GETDATE(),REORDERS.DateWritten) > 60)
  or (KeyIdentifiers.DeaClass <>'C-II'))


Good Luck,
Kent
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39934749
Imagine your CASE is a column. Then your existing WHERE clause actually says:

WHERE ColumnName

There is no comparison so the WHERE can't be evaluated. What you want to see is something like this:

WHERE ColumnName = SomeValue

or

WHERE ColumnName > SomeValue

etc.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39934781
SELECT Facilities.FacName, NursingStations.NsName, Facilities.FacID, KeyIdentifiers.DeaClass, vw_crystal_ReorderDirectionsExpandedSig.ExpandedSig, Reorders.CutoffDt, Reorders.LastRxNo, Reorders.LastFillDt, Reorders.Qty, Reorders.PhName, Patients.PatLName, Patients.PatFName, Reorders.TotalQtyWritten, Reorders.TotalQtyDispensed, Reorders.DateWritten, Reorders.DrugLabelName, Reorders.ProfileOnly
 FROM   ((((FwReports.dbo.Facilities Facilities INNER JOIN FwReports.dbo.NursingStations NursingStations ON Facilities.FacID=NursingStations.FacID) INNER JOIN FwReports.dbo.Patients Patients ON (NursingStations.FacID=Patients.FacID) AND (NursingStations.NsID=Patients.NsID)) INNER JOIN FwReports.dbo.Reorders Reorders ON (Patients.FacID=Reorders.FacID) AND (Patients.PatID=Reorders.PatID)) LEFT OUTER JOIN FwReports.dbo.vw_crystal_ReorderDirectionsExpandedSig vw_crystal_ReorderDirectionsExpandedSig ON ((Reorders.FacID=vw_crystal_ReorderDirectionsExpandedSig.FacID) AND (Reorders.RoNo=vw_crystal_ReorderDirectionsExpandedSig.RoNo)) AND (Reorders.PatID=vw_crystal_ReorderDirectionsExpandedSig.PatID)) LEFT OUTER JOIN FwReports.dbo.KeyIdentifiers KeyIdentifiers ON Reorders.NDC=KeyIdentifiers.NDC
 WHERE  (KeyIdentifiers.DeaClass='C-II' OR KeyIdentifiers.DeaClass='C-III' OR KeyIdentifiers.DeaClass='C-IV' OR KeyIdentifiers.DeaClass='C-V')
            AND Facilities.FacID='88'
            AND (Reorders.CutoffDt IS  NULL  OR Reorders.CutoffDt>={ts '2014-03-17 11:19:48'})
            AND Reorders.ProfileOnly=0 AND (Reorders.LastFillDt>={ts '2014-03-10 00:00:00'} AND Reorders.LastFillDt<{ts '2014-03-17 00:00:01'})
            AND (KeyIdentifiers.DeaClass='C-II' and DATEDIFF(DAY,GETDATE(),REORDERS.DateWritten) > 60)
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39935741
no points pl.

I'm not sure why you have used so many unnecessary parentheses in the from clause. It looks like a query one might use in Access which has a unique need for these; SQL Server (and most sql db's) does not need them.
SELECT
      Facilities.FacName
    , NursingStations.NsName
    , Facilities.FacID
    , KeyIdentifiers.DeaClass
    , vw_crystal_ReorderDirectionsExpandedSig.ExpandedSig
    , Reorders.CutoffDt
    , Reorders.LastRxNo
    , Reorders.LastFillDt
    , Reorders.Qty
    , Reorders.PhName
    , Patients.PatLName
    , Patients.PatFName
    , Reorders.TotalQtyWritten
    , Reorders.TotalQtyDispensed
    , Reorders.DateWritten
    , Reorders.DrugLabelName
    , Reorders.ProfileOnly
FROM FwReports.dbo.Facilities Facilities
      INNER JOIN FwReports.dbo.NursingStations NursingStations
            ON Facilities.FacID = NursingStations.FacID
      INNER JOIN FwReports.dbo.Patients Patients
            ON NursingStations.FacID = Patients.FacID
                  AND NursingStations.NsID = Patients.NsID
      INNER JOIN FwReports.dbo.Reorders Reorders
            ON Patients.FacID = Reorders.FacID
                  AND Patients.PatID = Reorders.PatID
      LEFT OUTER JOIN FwReports.dbo.vw_crystal_ReorderDirectionsExpandedSig vw_crystal_ReorderDirectionsExpandedSig
            ON Reorders.FacID = vw_crystal_ReorderDirectionsExpandedSig.FacID
                  AND Reorders.RoNo = vw_crystal_ReorderDirectionsExpandedSig.RoNo
                  AND Reorders.PatID = vw_crystal_ReorderDirectionsExpandedSig.PatID
      LEFT OUTER JOIN FwReports.dbo.KeyIdentifiers KeyIdentifiers
            ON Reorders.NDC = KeyIdentifiers.NDC
WHERE (KeyIdentifiers.DeaClass = 'C-II'
            OR KeyIdentifiers.DeaClass = 'C-III'
            OR KeyIdentifiers.DeaClass = 'C-IV'
            OR KeyIdentifiers.DeaClass = 'C-V')
      AND Facilities.FacID = '88'
      AND (Reorders.CutoffDt IS NULL
            OR Reorders.CutoffDt>={ts '2014-03-17 11:19:48'})
      AND Reorders.ProfileOnly=0 AND (Reorders.LastFillDt>={ts '2014-03-10 00:00:00'}
      AND Reorders.LastFillDt<{ts '2014-03-17 00:00:01'})
      AND (KeyIdentifiers.DeaClass = 'C-II'
            AND DATEDIFF(DAY, GETDATE(), REORDERS.DateWritten) > 60)

Open in new window

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

886 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