如何在查询中添加WHERE子句以避免“WHERE子句中的未知列”错误？

Question

    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;

我使用CASE WHEN语句创建了一个名为Merged_Date的变量，但我知道SQL是“向后”计算的，因此它抛出了一个未知列的错误。我假设我需要在这种情况下进行过滤，尽管我不知道如何正确地进行过滤。有人能帮忙吗？

Answer 1

当计算WHERE子句时，SQL不知道Merged_Date是什么。所以你必须在WHERE子句中详细说明。

替换

WHERE year(Merged_Date) = 2022

使用

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

或者，一种更简洁的方法

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