IIF(PRODS->COMCODE = "G",OUNCES,0) IIF(PRODS->COMCODE = "S",OUNCES,0) IIF(PRODS->COMCODE = "P",OUNCES,0) IIF(PRODS->COMCODE = "L",OUNCES,0)>Can you do this as one CASE statement (why do you need multiple)?
>>IIF(PRODS->COMCODE = "G",OUNCES,0) >>IIF(PRODS->COMCODE = "S",OUNCES,0) >>IIF(PRODS->COMCODE = "P",OUNCES,0) >>IIF(PRODS->COMCODE = "L",OUNCES,0) >>>>
>>DECLARE @CPosition int -- Needs to be explained by the customer >>SET @CPosition = 0 >> >>SELECT t.dtTrade_DT, >> sTrade_Amark_Trader, >> iTrade_ID, >> iOrder_Hdr_ID, >> sTrade_Type_CD, >> t.sProduct_CD, >> decTrade_Qty, >> @CPosition AS CPosition, >> decTrade_Unit_Price, >> decTrade_Spot_Price, >> CASE p.sCommodity_CD >> WHEN 'G' THEN t.decTrade_Qty * p.decProduct_Ozconv >> ELSE 0 >> END AS Goz >> CASE p.sCommodity_CD >> WHEN 'S' THEN t.decTrade_Qty * p.decProduct_Ozconv >> ELSE 0 >> END AS Soz >> CASE p.sCommodity_CD >> WHEN 'P' THEN t.decTrade_Qty * p.decProduct_Ozconv >> ELSE 0 >> END AS Poz >> CASE p.sCommodity_CD >> WHEN 'L' THEN t.decTrade_Qty * p.decProduct_Ozconv >> ELSE 0 >> END AS Loz >> FROM Trade t >> JOIN Product p on p.sProduct_CD = t.sProduct_CD >> WHERE t.sTrade_Type_Pay_Rec = 'PAY' OR >> t.sTrade_Type_Pay_Rec = 'REC' >> ORDER BY dtTrade_DT >>>>