Solved

CASE IN WHERE CLAUSE IN SQL

Posted on 2014-03-17
4
220 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 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

772 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

9 Experts available now in Live!

Get 1:1 Help Now