Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CASE IN WHERE CLAUSE IN SQL

Posted on 2014-03-17
4
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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