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 是“向后”評估的，因此它會引發未知列的錯誤。我假設我需要在 CASE WHEN 期間進行過濾，盡管我不知道如何正確執行。有人可以幫忙嗎？
uj5u.com熱心網友回復：
當評估 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/504978.html