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)
>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
>>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?
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people