August 31, 2004 at 3:16 pm
Hi all,
Using SQL Server 2000. Can I refer to a calculated field name from an expression within the SELECT statement?
For example:
SELECT
QTY,
COST,
TOTAL = QTY * COST,
NEW_TOTAL = TOTAL * 1.05
FROM ORDERS
My specific query (different than above) returns results when I have only the the first calculated field (TOTAL) but does *not* execute when I create the NEW_TOTAL expression and reference the TOTAL field.
Thanks for any help you can provide.
-Jason
August 31, 2004 at 3:34 pm
I don't believe it is directly possible. I'm assuming the computations for the real TOTAL are either intensive enough or complicated enough that you don't want to reperform them. Given that I can think of two ways to end up with the same net results. If the concern is not performance then a function could be used, but the computation would still be performed twice. If the computation is already a heavy hitter (because it IS a complicated function or a subselect or the like) then you can use a temp table to hold the initial results and then pull the data out of that.
DECLARE @Temp table ( Qty int, Cost money, Total money ) INSERT INTO @Temp SELECT QTY, COST, TOTAL = QTY * COST, FROM ORDERS SELECT *, New_Total = Total * 1.05 FROM @Temp |
August 31, 2004 at 3:35 pm
You cannot refer to a calculated column like in that way, it is not possible.
Try either:
SELECT
QTY,
COST,
TOTAL = QTY * COST,
NEW_TOTAL = QTY * COST * 1.05
FROM
ORDERS
SELECT
QTY,
COST,
TOTAL,
NEW_TOTAL = TOTAL * 1.05
FROM
( SELECT
QTY,
COST,
TOTAL = QTY * COST
FROM
ORDERS ) calct
/rockmoose
You must unlearn what You have learnt
August 31, 2004 at 3:50 pm
Thanks. Your plan B is what I was trying to do but forgot to name my resultset (calct). I reverted to the @Temp table when I couldn't figure out how to do that.
August 31, 2004 at 4:33 pm
You're welcome,
Well next time you will remember to alias your derived table 🙂
/rockmoose
You must unlearn what You have learnt
September 1, 2004 at 8:13 am
Thank you both for your helpful advice.
-Jason
September 3, 2004 at 9:59 am
Another option for this situation is to create a User Defined Function (UDF) to do the initial calculations and then refer to the UDF results in the final calculations
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply