Link to home
Create AccountLog in
Avatar of Mike Orther
Mike OrtherFlag for United States of America

asked on

Having issues with Case statement in my Where Clause

I am trying to add a Case statement to my where clause.  Essentially what I am trying to say is, If MinMax_Bin begins with "MFG", Then give me all of the IV00300.Bins that don't begin with "QC"


WHERE

TRIM(dbo.IV00300.BIN) = 

CASE WHEN Prosenthal_MinMaxDATA_ALLBins_Replenishment.MinMax_Bin LIKE 'MFG%' 

THEN TRIM(dbo.IV00300.BIN) NOT LIKE 'QC%' 

ELSE TRIM(dbo.IV00300.BIN)


I am receiving the following error when I include this Where Clause:

Error in list of function arguments: 'NOT' not recognized

Error in list of function arguments: ')' not recognized
Unable to parse query text.


Incorrect Syntax near the word 'NOT'


SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

If (Prosenthal_MinMaxDATA_ALLBins_Replenishment.MinMax_Bin NOT LIKE 'MFG%') is true, your condition doesn't check anything else, and my suggestion doesn't either.


(Prosenthal_MinMaxDATA_ALLBins_Replenishment.MinMax_Bin LIKE 'MFG%') isn't needed (for logic) because rows getting checked at that stage are already known to fulfill that condition, otherwise the NOT LIKE 'MFG%' would have been fitting.


So my suggestion is logical correct but not obvious.

Hi Qlemo,

sorry, you're right.
I have overlooked that you used "NOT LIKE 'MFG%'".

So yours is shorter and comes to the same result as mine.

Cheers,

Christian
Avatar of Mike Orther

ASKER

WHERE  (Prosenthal_MinMaxDATA_ALLBins_Replenishment.MinMax_Bin LIKE 'MFG%' 
        AND TRIM(dbo.IV00300.BIN) NOT LIKE 'QC%')

Open in new window


OMG why was I trying to make this way more difficult than it needed to be, UGHHH!  Thank you both for solution.

That's exactly what I wrote as the first comment on this q.

Sorry Scott.  
You are correct, this is exactly what you wrote and I have checked "MARK AS A SOLUTION"