>Each one of these is a seperate column on the report. If the CodeCode doesn't match, the column displays 0. So each would have to appear as a seperate column in the result set.
>
>

>IIF(PRODS->COMCODE = "G",OUNCES,0)
>IIF(PRODS->COMCODE = "S",OUNCES,0)
>IIF(PRODS->COMCODE = "P",OUNCES,0)
>IIF(PRODS->COMCODE = "L",OUNCES,0)
>

>

I see. Try (CASE WHEN ComCode = 'G' THEN ... ELSE 0) as GOZ
>
>
>>Can you do this as one CASE statement (why do you need multiple)?
>>
>>CASE WHEN p.sCommodity_CD IN ("G","S",..) G THEN t.decTrade_Qty * p.decProduct_Ozconv ELSE 0 END as ...
>>
>>>I am converting some R&R reports to Crystal.Net. One of the reports has this logic on the detail line:
>>>
>>>
>>>

>>>IIF(PRODS->COMCODE = "G",OUNCES,0)
>>>IIF(PRODS->COMCODE = "S",OUNCES,0)
>>>IIF(PRODS->COMCODE = "P",OUNCES,0)
>>>IIF(PRODS->COMCODE = "L",OUNCES,0)
>>>

>>>
>>>
>>>I want to build this logic into my query, but somethig isn't quite right. I'm not entirely sure this is the way to do this.
>>>
>>>

>>>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
>>>

>>>
>>>Anyone know a better way to do this?