asked on

SQL - Syntax error when using multiple Case When conditions

I have a SQL report that I have added Case When statements to. When I run the report I get a Syntax Error near "OR" in statement. Can you see why:

      CASE WHEN ((RAQ_R1.Query_MisQryID = 'PNRASPV' AND RAQ_R1.ValueInfo = 'Y') AND
             (RAQ_R1.Query_MisQryID = 'PNRRESHE' AND RAQ_R1.ValueInfo = 'Tracheostomy') AND
             (RAQ_R1.Query_MisQryID = 'PNRSTCO' AND RAQ_R1.ValueInfo = 'N') AND
             (RAQ_R1.Query_MisQryID = 'PNRHXCVA01' AND RAQ_R1.ValueInfo = 'Y')) AND OO_M2.Procedure_OmOrdDictID = 'SEC.ST.RAD.BMH'
             THEN 'Yes' OR
             ((RAQ_R1.Query_MisQryID = 'PNRASPV' AND RAQ_R1.ValueInfo = 'Y') AND
             (RAQ_R1.Query_MisQryID = 'PNRRESHE' AND RAQ_R1.ValueInfo = 'Tracheostomy') AND
             (RAQ_R1.Query_MisQryID = 'PNRSTCO' AND RAQ_R1.ValueInfo = 'N') AND
             (RAQ_R1.Query_MisQryID = 'PNRHXCVA01' AND RAQ_R1.ValueInfo = 'Y')) AND OO_M2.Procedure_OmOrdDictID <> 'SEC.ST.RAD.BMH'
             THEN 'No' END AS 'Order Placed'


KR

Scott Pletcher

Yes I can.

You need to start a WHEN to apply the next set of conditions:

THEN 'Yes' --OR --<<-- remove the OR
WHEN --add the WHEN
((RAQ_R1.Query_MisQryID = 'PNRASPV' AND RAQ_R1.ValueInfo = 'Y') AND

kvrogers

ASKER

So the outcome of this is she wants to know Yes an order was placed or No an order was not placed. The only difference between the two CASE WHEN statements is in the last line of the second CASE WHEN, where the OmOrdDictID is NOT EQUAL 'SEC.ST.RAD.BMH'

I entered your suggestion above :
THEN 'Yes'
            WHEN ((RAQ_R1.Query_MisQryID = 'PNRASPV' AND RAQ_R1.ValueInfo = 'Y') AND
             (RAQ_R1.Query_MisQryID = 'PNRRESHE' AND RAQ_R1.ValueInfo = 'Tracheostomy') AND
             (RAQ_R1.Query_MisQryID = 'PNRSTCO' AND RAQ_R1.ValueInfo = 'N') AND
             (RAQ_R1.Query_MisQryID = 'PNRHXCVA01' AND RAQ_R1.ValueInfo = 'Y')) AND OO_M2.Procedure_OmOrdDictID <> 'SEC.ST.RAD.BMH'
             THEN 'No' END AS 'Order Placed'

and in the report my column "Order Placed" it says NULL. I would like it to say Yes (an order was placed) or NO (an order was not placed.

KR

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kvrogers

ASKER

Very Helpful.