How do I use a WHERE clause on a user-defined variable from a CASE WHEN?

Question

SELECT #Opportunity Details
       o.Id AS 'Opportunity ID',
       o.Name As 'Opportunity',
       o.Project_Assigned__c AS 'PM Assign Date',
      CASE WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
            THEN 'Pre-Sales' 
           WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
            THEN 'Omit' 
           WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
            THEN 'Lost' ELSE o.StageName END AS 'SFDC Forecast Group',   
      #Account Details
      a.Name AS 'Account Name',
      a.Global_Region__c AS 'Account Region'  
FROM SFDC.Opportunity o
LEFT JOIN SFDC.Account a ON a.Id = o.AccountId
WHERE 'SFDC Forecast Group' IN ('Won','Pre-Sales','Invoice','Omit')

How do I use a WHERE clause with my user-created variable 'SFDC Forecast Group'? When I run this query, it returns zero rows. I have read solutions where people use SET or DECLARE but I don't understand how/what that means or how to do it in my code.

Does this answer your question? [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) (This is true for MySQL and for SQL Server.) — Luuk, Sep 23 '22 at 13:18
@Luuk Yes, it sort of does (I was hoping I didn't have to do it the first way he mentions.) Can you tell me if there are any drawbacks to forcing SQL to evaluate the SELECT statement first (as shown in the selected answer)? If you can, I'd still appreciate an example of how to do the first method with my CASE WHEN.. — Stephen Poole, Sep 23 '22 at 13:27
MySQl should be optimized in such a way that there should be no negative impact on speed. (or other negative aspect) — Luuk, Sep 23 '22 at 13:30

Luuk · Accepted Answer · 2022-09-23T13:41:59.890

First way to get this query going:

SELECT #Opportunity Details
       o.Id AS 'Opportunity ID',
       o.Name As 'Opportunity',
       o.Project_Assigned__c AS 'PM Assign Date',
      CASE WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
            THEN 'Pre-Sales' 
           WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
            THEN 'Omit' 
           WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
            THEN 'Lost' ELSE o.StageName END AS 'SFDC Forecast Group',   
      #Account Details
      a.Name AS 'Account Name',
      a.Global_Region__c AS 'Account Region'  
FROM SFDC.Opportunity o
LEFT JOIN SFDC.Account a ON a.Id = o.AccountId
WHERE CASE WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
            THEN 'Pre-Sales' 
           WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
            THEN 'Omit' 
           WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
            THEN 'Lost' ELSE o.StageName END IN ('Won','Pre-Sales','Invoice','Omit')

Second possibility, make it a sub-select. After this you can refer to the column 'SFDC Forecast Group':

SELECT
  Details,
  Opportunity,
  'PM Assign Date',
  'SFDC Forecast Group',
  'Account Name',
  'Account Region'
FROM (
   SELECT #Opportunity Details
       o.Id AS 'Opportunity ID',
       o.Name As 'Opportunity',
       o.Project_Assigned__c AS 'PM Assign Date',
      CASE WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
            THEN 'Pre-Sales' 
           WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
            THEN 'Omit' 
           WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
            THEN 'Lost' ELSE o.StageName END AS 'SFDC Forecast Group',   
      #Account Details
      a.Name AS 'Account Name',
      a.Global_Region__c AS 'Account Region'  
   FROM SFDC.Opportunity o
   LEFT JOIN SFDC.Account a ON a.Id = o.AccountId
)x
WHERE 'SFDC Forecast Group' IN ('Won','Pre-Sales','Invoice','Omit')

another option would be to use a CTE.

EDIT (about performance of this):

Suppose I have a table integers with 2621442 rows in it. Then MySQL does not show a difference in doing:

select i from integers where i=10;

or

select x from (select i as x from integers) x where x=10;

A select count(*) from integers does take 0.29 sec, but both queries above will finish in 0.0 sec.

Ah, the END IN is the part I didn't know. What does the x mean after the parenthesis on the sub-select? — Stephen Poole, Sep 23 '22 at 13:36
Okay, I have one other issue. When I run the first version of the query, I'm not seeing 'Lost' or 'Rejected' but I am seeing 'Omit from forecast,' which should have been renamed as simply 'Omit.' I'm also not seeing anything 'Pre-Sales'... Is there a typo possibly? — Stephen Poole, Sep 23 '22 at 13:48
Yes, that's possible, but I do not see a typo in the piece I copied from your code.... — Luuk, Sep 23 '22 at 13:53

score 0 · Answer 2 · answered Sep 23 '22 at 13:31

You can use with statements to create a subquery first to make your classification and then filter it. Example:

WITH cte as (
   SELECT #Opportunity Details
       o.Id AS 'Opportunity ID',
       o.Name As 'Opportunity',
       o.Project_Assigned__c AS 'PM Assign Date',
      CASE WHEN o.StageName IN ('Best Case','Commit','Committed','Discover and Assess','Pipeline','Probable','Sales Qualification','Upside') 
            THEN 'Pre-Sales' 
           WHEN o.StageName IN ('Omit - Closed','Omit from Forecast','Omit-Closed','Omitted') 
            THEN 'Omit' 
           WHEN o.StageName IN ('Rejected','Lost','Cancelled') 
            THEN 'Lost' ELSE o.StageName END AS 'SFDC Forecast Group',   
      #Account Details
      a.Name AS 'Account Name',
      a.Global_Region__c AS 'Account Region'  
FROM SFDC.Opportunity o
LEFT JOIN SFDC.Account a ON a.Id = o.AccountId )

select 
   * 
from cte
WHERE 'SFDC Forecast Group' IN ('Won','Pre-Sales','Invoice','Omit')

Another solution is to simply replicate the statements used in the CASE within the WHERE clause

How do I use a WHERE clause on a user-defined variable from a CASE WHEN?

2 Answers2