Star79
asked on
CASE IN WHERE CLAUSE IN SQL
Hello,
I have the below query:
Not sure why.Please help.
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
When I execute this it says incorrect syntax near '>'Not sure why.Please help.
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.
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.
SELECT Facilities.FacName, NursingStations.NsName, Facilities.FacID, KeyIdentifiers.DeaClass, vw_crystal_ReorderDirectio nsExpanded Sig.Expand edSig, 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.Faciliti es Facilities INNER JOIN FwReports.dbo.NursingStati ons NursingStations ON Facilities.FacID=NursingSt ations.Fac ID) INNER JOIN FwReports.dbo.Patients Patients ON (NursingStations.FacID=Pat ients.FacI D) AND (NursingStations.NsID=Pati ents.NsID) ) INNER JOIN FwReports.dbo.Reorders Reorders ON (Patients.FacID=Reorders.F acID) AND (Patients.PatID=Reorders.P atID)) LEFT OUTER JOIN FwReports.dbo.vw_crystal_R eorderDire ctionsExpa ndedSig vw_crystal_ReorderDirectio nsExpanded Sig ON ((Reorders.FacID=vw_crysta l_ReorderD irectionsE xpandedSig .FacID) AND (Reorders.RoNo=vw_crystal_ ReorderDir ectionsExp andedSig.R oNo)) AND (Reorders.PatID=vw_crystal _ReorderDi rectionsEx pandedSig. PatID)) LEFT OUTER JOIN FwReports.dbo.KeyIdentifie rs KeyIdentifiers ON Reorders.NDC=KeyIdentifier s.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(),REO RDERS.Date Written) > 60)
FROM ((((FwReports.dbo.Faciliti
WHERE (KeyIdentifiers.DeaClass='
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='
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CASE returns a value, not an expression or clause. :( But it's easy to fix.
and
case
when KeyIdentifiers.DeaClass='C
END
becomes:
and ((KeyIdentifiers.DeaClass=
or (KeyIdentifiers.DeaClass <>'C-II'))
Good Luck,
Kent