How do I add a WHERE clause to my query to avoid the 'unknown column in WHERE clause' erro-CodePudding

    select
    qs.Id, 
    qs.Opportunity__c,
    qs.Name as `Product Name`,
    so.Name as `Opportunity Name`,
    so.CloseDate AS 'Opp Close Date',
    so.Project_Assigned__c AS 'Project Assign Date',
    soi.Product_Family__c,
    
    # Vendor Details    
    spv.Vendor_Name__c AS 'Vendor',
    spv.Selected_for_Use__c AS 'Selected for Use', 
    spv.CurrencyIsoCode AS 'Currency',
    spv.Total_Vendor_Quoted_Cost__c AS 'Quoted Cost',
    spv.Approved_Cost__c AS 'Approved Cost',
    spv.CurrencyIsoCode,

CASE WHEN spv.Approved_Cost__c IS NOT NULL
    THEN spv.Approved_Cost__c
    ELSE spv.Total_Vendor_Quoted_Cost__c
    END AS Cost,
    
CASE WHEN so.Project_Assigned__c IS NOT NULL
    THEN so.Project_Assigned__c 
    ELSE so.CloseDate
    END AS Merged_Date,

from SFDC.QService__c qs

left join SFDC.Opportunity so ON so.Id = qs.Opportunity__c
left join SFDC.OpportunityLineItem soi ON soi.OpportunityId = qs.Opportunity__c
left join SFDC.Panels_Project_Vendor__c spv ON spv.Opportunity__c = so.Id

WHERE year(Merged_Date) = 2022

GROUP BY qs.Opportunity__c, spv.Vendor_Name__c, spv.CurrencyIsoCode
ORDER BY Merged_Date DESC;

I created a variable called Merged_Date using a CASE WHEN statement, but I know SQL is evaluated "backwards" so it's throwing an error of unknown column. I assume I need to do my filtering during the CASE WHEN, though I don't know how to do it correctly. Can someone help?

CodePudding user response：

SQL doesn't know what Merged_Date is when your WHERE clause is evaluated. So you have to detail it in the WHERE clause.

Replace

WHERE year(Merged_Date) = 2022

With

WHERE year(CASE WHEN so.Project_Assigned__c IS NOT NULL
   THEN so.Project_Assigned__c 
   ELSE so.CloseDate
   END) = 2022

Alternatively, a more succinct approach

WHERE year(COALESCE(so.Project_Assigned__c, so.CloseDate)) = 2022