Help with CASE in a WHERE Clause

1

I have a query that is working well, except that users have found a way to screw with the data, so I have to account for the users mistakes.

SELECT DISTINCT 

CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) AS decimal (10,2)) AS FBGM, 
ROUND(ISNULL(qbd.Profit_Percent+cd.Commission_Percent,qbd.Profit_Percent),2) AS Budgeted,
oh.Order_Number AS Order_Number,
od.Work_Code AS Work_Code,
sc.Address__Email_Address AS Email_Address,
sco.Salesman_Name AS Salesman_Name,
cd.Commission_Percent AS Commission_Percent,
od.Employee_Code_Routed_By AS Routed_By,
od.Product_Code AS Product_Code,
od.Part_Number AS Part_Number,
CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) *.9 AS decimal (10,2))  AS FBGMx9,
CAST(COALESCE(LEFT(od.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS FBGMx11,
uc.EMail_Address_Primary AS EMail_Address_Primary, 
cd.Commissionable AS Commissionable

FROM dbo.Order_Detail od 
LEFT OUTER JOIN dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail qbd ON od.Order_Detail_ID = qbd.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail bd ON oh.Part_Number_ID = bd.Part_Number_ID
LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders sco ON od.Order_Detail_ID = 
sco.Order_Detail_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON od.Order_Detail_ID = cd.Order_Detail_ID
LEFT OUTER JOIN dbo.vSalesman_Code sc ON cd.Salesman_Code = sc.Salesman_Code
LEFT OUTER JOIN dbo.Employee_Code ec ON od.Employee_Code_Routed_By = 
ec.Employee_Code
LEFT OUTER JOIN dbo.User_Code uc ON ec.User_Code = uc.User_Code

WHERE	  
qbd.Profit_Percent  <>  0 AND
od.Profit_Percent  <>  0 AND
od.Status  NOT IN  ( 'Closed' , 'Hold' , 'Planned' ) AND
od.Part_Family_Code  NOT IN  ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND
(CASE WHEN cd.Commissionable = 1 THEN ((ROUND(qbd.Profit_Percent + 
cd.Commission_Percent, 2) NOT BETWEEN  CONVERT(INT, LEFT(od.Part_Family_Code,2)) -5 AND 
CONVERT(INT, LEFT(od.Part_Family_Code,2)) +5)) ELSE
((ROUND(qbd.Profit_Percent, 2) NOT BETWEEN  CONVERT(INT, LEFT(od.Part_Family_Code,2)) -5 
AND CONVERT(INT, LEFT(od.Part_Family_Code,2)) +5)) END)

ORDER BY sco.Salesman_Name ASC

I've added the CASE statement and I'm getting the error "Incorrect Syntax Near the Keyword NOT"

I'm not 100% sure if the CASE statement is in the correct format. When a user checks the Pay Commission Box, it changes the record in cd.Commissable from 0 to 1. When they do that I have to add the commission rate back in (qbd.Profit_Percent + cd.Commission_Percent) If they forget, I need to know that and not add the commission rate back in.

Any help would be appreciated.

2

A CASE result returns a single value only and thus cannot contain a comparison or keyword. You just need a slight adjustment:

WHERE	  
qbd.Profit_Percent  <>  0 AND
od.Profit_Percent  <>  0 AND
od.Status  NOT IN  ( 'Closed' , 'Hold' , 'Planned' ) AND
od.Part_Family_Code  NOT IN  ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND
(CASE WHEN cd.Commissionable = 1 
      THEN ROUND(qbd.Profit_Percent + cd.Commission_Percent, 2) 
      ELSE ROUND(qbd.Profit_Percent, 2) END 
NOT BETWEEN  CONVERT(INT, LEFT(od.Part_Family_Code,2)) -5 AND 
CONVERT(INT, LEFT(od.Part_Family_Code,2)) +5)
2 Likes
3

Not sure how you going to change the values if the case statement is in the WHERE part of the statement. It would expect to find it in the SELECTpart.

SELECT CASE WHEN cd.Commissionable = 1 THEN 'some formula' ELSE 'some other formula END
FROM TABLES
WHERE.....

4

Lewie, please look at the original question. Also you can use case statement in a where clause when your filter is conditional

5

Scott,

Thank you so much, that makes perfect sense now. I was thinking about it completely wrong. I now see I'm only looking to conditionalize one aspect of my filter.

Thanks alot.