Solved

CASE IN WHERE CLAUSE IN SQL

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

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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 47
How can I use this function? 3 35
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 63
t-sql left join 2 34
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

738 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