CODEWITHSUNDEEP
sqlsql-server-2012case
Mimi
Mimi

Reputation: 389

Define new columns using values of a row in sql

the below code works

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)

But I want another column as result of the case condition. I tried this,

CASE WHEN PPC.FactoryName = ''ANANTA'' THEN OM.OrderQty END AS Unit1_Qty,
Unit1_Qty * FR.Rate as Unit1_Value

and this 

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

But they dont work. What can I do now?

Upvotes: 1

Views: 28

Answers (1)

ASh
ASh

Reputation: 35681

do not refer to alias of result field in SELECT statement

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)

Upvotes: 1

Related Questions