Solved

CASE IN WHERE CLAUSE IN SQL

Posted on 2014-03-17
4
224 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
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 39

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

786 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