下面的代码有效
SET @SQL = '
SELECT SC.Name AS BUYER,
CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty
FROM LineAllocation LA
INNER JOIN OrderMaster OM ON LA.OrderRef= OM.OrderRefID
INNER JOIN AmanOTS..FileRef FR ON FR.FileRefID = OM.FileRefID
INNER JOIN SystemManager..Contacts SC ON SC.Code=OM.BuyerCode
INNER JOIN SystemManager..ProductionProcessCostCenter PPC ON PPC.CostCenter= LA.Line
'
Exec(@sql)
但是，我想根据情况来另写一栏。我试过了
CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty,
Unit1_Qty * FR.Rate as Unit1_Value
还有这个
CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty,
CASE WHEN PPC.FactoryName = ''ANANTA'' THEN Unit1_Qty * FR.Rate END AS Unit1_Value
但是他们不起作用。我现在能做什么？
不要在SELECT语句中引用结果字段的别名
SET @SQL = '
SELECT SC.Name AS BUYER,
CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty,
-- OM.OrderQty instead of Unit1_Qty
CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty * FR.Rate END AS Unit1_Value
FROM LineAllocation LA
INNER JOIN OrderMaster OM ON LA.OrderRef= OM.OrderRefID
INNER JOIN AmanOTS..FileRef FR ON FR.FileRefID = OM.FileRefID
INNER JOIN SystemManager..Contacts SC ON SC.Code=OM.BuyerCode
INNER JOIN SystemManager..ProductionProcessCostCenter PPC ON PPC.CostCenter= LA.Line
'
Exec(@sql)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句