How to put a case statement in a where clause

Question

Post reply

How to put a case statement in a where clause

itmasterw 60042

SSCrazy

Points: 2858
More actions
October 20, 2022 at 2:13 pm

#4101995
Hi,
I am trying to put a case statement in a where clause and nothing that I am doing seems to work, that I got online.
Basically, I need to put in the where clause that if the state = MN then these ProcCodes are Excluded but in any other state then they and others go.
Here is my where clause:
```
WHERE MyState = 'MN' THEN ProcCode NOT IN ('Kl', 'Kjh') ELSE PrcCode ('kl', 'Kjh', 'bn') END
AND PayDt > '12/01/20'
```
Thnak you

January 10, 2024 at 6:55 am

This was removed by the editor as SPAM
January 10, 2024 at 7:00 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

itmasterw 60042 wrote:

Hi,
I am trying to put a case statement in a where clause and nothing that I am doing seems to work, that I got online.
Basically, I need to put in the where clause that if the state = MN then these ProcCodes are Excluded but in any other state then they and others go.
Here is my where clause:
WHERE MyState = 'MN' THEN ProcCode NOT IN ('Kl', 'Kjh') ELSE PrcCode ('kl', 'Kjh', 'bn') END
AND PayDt > '12/01/20'
Thnak you

You're missing the CASE in your CASE expression.

The main problem is that you are trying to return a Boolean value from your CASE expression, but CASE will never return a Boolean value. This is because SQL Server does not recognize a Boolean data type.

You need to return a specific (set of) value(s) and then test the returned value. Something like

WHERE CASE WHEN MyState <> 'MN' THEN 1
           WHEN ProcCode NOT IN ('Kl', 'Kjh') THEN 1
           ELSE 0
      END = 1
AND PayDt > '12/01/20'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Thanks for the reply, but that is not what I need.

I need a way to write this that when the state is MN then these ProcCodes are Excluded, but in any other state then they are good.

Do you know how AI can write that?

Thank you

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

itmasterw 60042 wrote:

Thanks for the reply, but that is not what I need.
I need a way to write this that when the state is MN then these ProcCodes are Excluded, but in any other state then they are good.
Do you know how AI can write that?
Thank you

I don't think you need a CASE statement, you just need an OR.

WHERE (State = 'MN' AND ProdCodes IN ('Kl', 'Kjh') )
   OR State <> 'MN'

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

That may work but I need something a little different:

I need the if it is State = MN then Exclude ProdCodes IN ('Kl', 'Kjh') Else if State not = MN then allow them.

But I do not know how to write this.

Thank you

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

itmasterw 60042 wrote:

That may work but I need something a little different:
I need the if it is State = MN then Exclude ProdCodes IN ('Kl', 'Kjh') Else if State not = MN then allow them.
But I do not know how to write this.
Thank you

WHERE (State = 'MN' 
       AND ProdCodes NOT IN ('Kl', 'Kjh') )
   OR State <> 'MN'

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

This code would check for State = 'MN'

AND ProdCodes NOT IN ('Kl', 'Kjh') )

I need it to exclude ProdCodes NOT IN ('Kl', 'Kjh') When the state is MN.

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Please post sample data and expected outcome.

Please follow the guidance here

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

itmasterw 60042 wrote:

This code would check for State = 'MN' AND ProdCodes NOT IN ('Kl', 'Kjh') )
I need it to exclude ProdCodes NOT IN ('Kl', 'Kjh') When the state is MN.

That's what the first SQL statement I pasted in did.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

IF OBJECT_ID('tempdb..#temp','u') is not null
    DROP TABLE #temp
GO
SELECT *
  INTO #temp
  FROM (VALUES ('MN', 'KL'),
               ('MN', 'Kjh'),
               ('MN', 'XYZ'),
               ('XX', 'KL'),
               ('XX', 'Kjh'),
               ('XX', 'ABC')) T(State, ProdCodes)
GO
SELECT * 
  FROM #temp
GO
SELECT *
  FROM #temp
 WHERE (State = 'MN' AND ProdCodes IN ('Kl', 'Kjh') )
    OR State <> 'MN'
GO
SELECT *
  FROM #temp
 WHERE (State = 'MN' 
        AND ProdCodes NOT IN ('Kl', 'Kjh') )
    OR State <> 'MN'

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 10

itmasterw 60042

SSCrazy

Points: 2858

October 20, 2022 at 4:35 pm

#4102091

okay, this works.

Thanks for the help

drew.allen SSC Guru Points: 76985 More actions · Answer 11

itmasterw 60042 wrote:

Thanks for the reply, but that is not what I need.
I need a way to write this that when the state is MN then these ProcCodes are Excluded, but in any other state then they are good.
Do you know how AI can write that?
Thank you

Your responses show that you are not bothering to read the suggestions and figure out how they work. My response does EXACTLY what you asked for, it's just evaluated in a slightly different order.

WHEN MyState <> 'MN' THEN 1 -- "in any other state then they are good"

WHEN ProcCode NOT IN ('Kl', 'Kjh') THEN 1 -- we know MyState = 'MN' in order to get to this point and this includes every ProcCode except the ones you want to exclude.

ELSE 0 -- we know that MyState = 'MN' and ProcCode in ('Kl', 'Kjh') to get to this point, and "these ProcCodes are Excluded"

You had a very similar response to Jonathan AC Roberts.

If you expect us to put in the work to help you, then you need to demonstrate that you are also willing to put in the work, and you clearly are showing that you're not willing to put in the work.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA