CODEWITHSUNDEEP
sqlsql-server
emie
emie

Reputation: 289

SQL query CASE statement with WHERE clause

I have built the following query that utilizes a multi condition CASE statement. Is this the proper/best way to have a multi condition CASE statement?

It works as longs as I do not reference the CASE statement within the where. I get "Invalid column name 'Scenario'."

How do I reference ScenarioA within the WHERE portion of the query? 

select 
    TPID
    ,(CASE WHEN 
        ([AOppty] > 0) and ([Entitlements] = 0) and ([B Agreement] = 0 and [C Agreement] = 0)
    THEN 1 ELSE 0 END) as ScenarioA
from RulesEngine R
--where ScenarioA = 1

Upvotes: 2

Views: 67

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You can't. You can use a subquery and where clause:

 select r.*
 from (select TPID,
              (CASE WHEN [M365 E3 Oppty] > 0) and ([EMS Entitlements] = 0) and ([EMS E3 Agreement] = 0 and [EMS E5 Agreement] = 0
                    THEN 1 ELSE 0
               END) as ScenarioA
       from RulesEngine R
      ) R
 where ScenarioA . . .

Of course, for this simple situation, it is simpler to do:

select TPID, 1 as ScenarioA
from RulesEngine R
where [M365 E3 Oppty] > 0) and ([EMS Entitlements] = 0) and ([EMS E3 Agreement] = 0 and [EMS E5 Agreement] = 0;

The CASE doesn't add much.

For more complicated expressions, SQL Server also has cross apply:

select r.tpid, v.ScenarioA
from RulesEngine r cross apply
     (values ( CASE WHEN [M365 E3 Oppty] > 0) and ([EMS Entitlements] = 0) and ([EMS E3 Agreement] = 0 and [EMS E5 Agreement] = 0
                    THEN 1 ELSE 0
               END
             )
      ) v(ScenarioA)
 where v.ScenarioA . . .;

In my opinion, cross apply is the cleanest solution, because it makes it easy to add multiple expressions that depend on each other, without using sub-sub queries and multiple CTEs.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727047

SQL Server lets you use a common table expression for this:

WITH
    Rules (TPID, ScenarioA)
AS (
    SELECT 
        TPID
    ,   (CASE WHEN 
            ([AOppty] > 0) AND ([Entitlements] = 0) AND ([B Agreement] = 0 and [C Agreement] = 0)
            THEN 1 ELSE 0 END
        ) AS ScenarioA
)
SELECT
    TPID, ScenarioA
WHERE
    ScenarioA = ...

Upvotes: 3

Jaspreet Singh
Jaspreet Singh

Reputation: 131

Use it as a subquery.

select * from 
(
select 
    TPID
    ,(CASE WHEN 
        ([M365 E3 Oppty] > 0) and ([EMS Entitlements] = 0) and ([EMS E3 Agreement] = 0 and [EMS E5 Agreement] = 0)
    THEN 1 ELSE 0 END) as ScenarioA
from RulesEngine
) R where ScenarioA = 1

This should ideally work with most RDBMS.

Upvotes: 0

Related Questions