Refer to calculated field in expressions?
-
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
-
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
-
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 -
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.
-
You're welcome,
Well next time you will remember to alias your derived table
/rockmoose
You must unlearn what You have learnt -
Thank you both for your helpful advice.
-Jason
-
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply