Using Right() in case statement

I have a query like the following:

Select Item, StartDate,
Case when Right(lot, 1) in ('A', 'B', 'C') then 'Suffixed'
else '' end as ItemNote
From Table1

This doesn't seem to like the "IN ('A', 'B', 'C')". What can I use other then writing a long "Or This" string.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you getting an error message?  Because I don't see anything wrong with your statement syntactically.
Scott PletcherSenior DBACommented:
Yep, nothing wrong with that, it should work just fine.
Jim HornMicrosoft SQL Server Data DudeCommented:
Define 'not like'.  Does it return an error message?  Returns zero rows?  Doesn't have a cool enough car / not enough money / can't sing?

Make sure all the table and column names are spelled correctly
Make sure you're in the correct database
Does lot have any NULL values, which won't equate to anything unless you handle it?  CASE when Right(ISNULL(lot, 'x'), 1) THEN ...
Is lot a char/varchar value?  If it's numeric, then the expression equating it to '1', '2', will reply on implicit conversion, which may throw an error.
Are you sure that 'what's not being liked' is that statement, and not something before or after?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MadIceAuthor Commented:
In the results it will add the note to all the ones it should but will also add to some that it shouldn't. For example:

this one adds the note and shouldn't. where the following it doesn't:

The Right() seems to be no problem. I Took out the In portion and made it = 'A' just to test. No problem. I might just need to do update statements after the initial query. It just bugs me I can't see the issue. I also can't post the actual query for further help.
Scott PletcherSenior DBACommented:
For example:
 this one adds the note and shouldn't. where the following it doesn't:

Hmm, something odd going on there.  Are those the values in the "lot" column, that is, the column you are testing?  That's definitely not 'A'/'B'/'C' so, again, the issue must be some other condition / code in the query.
MadIceAuthor Commented:
Okay, this is embarrassing. When I changed to an update query, it was easier to see the issue. It's working as asked it too. Problem was I had zero where the O should of been. I gave a bad example before.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Madice, your issues was resolved?
If not, do you want to reformulate it?
Jim HornMicrosoft SQL Server Data DudeCommented:
Works for us.  Thanks for the split and for posting the eventual answer.  Good luck with your project.  -Jim
MadIceAuthor Commented:
Since the issue was a type O, I'm just spreading the points out for looking over the question. Thanks for the assistance. This should of been closed out before. Not sure what happen. Sorry for the delay.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.