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

Stephen Poole 0
Viewed 23
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.

mysql-workbench mysql sql
2 Answers

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.

Luuk0

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

Rodrigo Cava0
Related