Nope,
you can't escape from CASE structure if you want to get this from your result set. Otherwise if to manage when print has to be printed in your CR:
SELECT ....
p.sCommodity_CD,
t.decTrade_Qty * p.decProduct_Ozconv AS MyField
....
Then in CR you should have MyField placed on four different locations and add logic when if should be printed.
>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?
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.