You can not use computed fields this way, you can only use them in derived tables. Otherwise you have to repeat the computation again.
See bellow:
>
SELECT NetAmount,
(t.decTrade_Product_Balance / t.decTrade_Qty) * NetAmount AS NotShip
FROM Trade t INNER JOIN
(select t.decTrade_Ext_Price + t.decTrade_Shipping_Charge + t.decTrade_Other_Charge
AS NetAmount from Trade) Net on Net.iTrading_Partner_ID = t.iTrading_Partner_ID
JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID
JOIN Product p ON p.sProduct_CD = t.sProduct_CD
>Why doesn't this work? I'm trying to first compute NetAmount, then use it in the next column:
>
>
>
>SELECT t.decTrade_Ext_Price + t.decTrade_Shipping_Charge + t.decTrade_Other_Charge AS NetAmount,
> (t.decTrade_Product_Balance / t.decTrade_Qty) * NetAmount AS NotShip
> FROM Trade t
> JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID
> JOIN Product p ON p.sProduct_CD = t.sProduct_CD
>
>
>
>However, this does work with the computation included in the column
>
>
>SELECT (t.decTrade_Product_Balance / t.decTrade_Qty) * (t.decTrade_Ext_Price + t.decTrade_Shipping_Charge + t.decTrade_Other_Charge) AS NotShip
> FROM Trade t
> JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID
> JOIN Product p ON p.sProduct_CD = t.sProduct_CD
>
If it's not broken, fix it until it is.
My Blog