T-SQL "shortcut"?

Question

T-SQL "shortcut"?

jnelson

SSC Enthusiast

Points: 100
More actions
August 28, 2003 at 4:54 pm

#101644

The following example is simplified from my real-world problem, but basically I need to know if something like this is possible:
select (colA+colB) as sum1,
sum1/colC as calc1
from SomeTable
This T-SQL does not work, as I cannot reference the first column by its alias. Obviously, I can express the second column as (colA+colB)/colC (seems redundant and less efficient), but is there a way to build upon something that has already been calculated within the select statement?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1



Create Table Tmp(ColA Money,COlB Money,ColC Money)

GO

Insert  Tmp Values(1,2,3)

Insert  Tmp Values(4,5,6)

Insert  Tmp Values(7,8,9)

GO

Select ASum,Asum/ColC

From (

Select ColA+ColB as ASum,ColC

From Tmp) as F(ASum,COlC)

GO

Drop Table Tmp

Lightning SSC Enthusiast Points: 124 More actions · Answer 2

How about:

DECLARE @sum1 int

SELECT @sum1 = colA + colB

FROM SomeTable

SELECT @sum1/colC

FROM SomeTable

GO

waviedavie SSC Veteran Points: 265 More actions · Answer 3

Jnelson,

There is a much easier way that does not require variables or temp tables. Just use a derived table as follows:

select sum1/colc

from sometable s1

inner join (select (cola + colb) as Sum1,

iRowId

from sometable) as s2

on s1.iRowId = s2.iRowId

This example assumes that iRowId is a PK, or unique valued column.

Best of luck!

wavie davie 🙂

jnelson SSC Enthusiast Points: 100 More actions · Answer 4

A sincere thanks to those who replied.

Mr. 5409045121009: What does the F(..) do? I haven't seen this function before? It does work, I just don't understand it.

Lightning: The value returned by the sum is for the last row only, and I need the sum for each row.

waviedavie: This is very clear and works.

satishbt SSC Eights! Points: 828 More actions · Answer 5

Well Jnelson, Mr. 5409045121009 is using corelated Sub-query. Thats one way of doing it. The other way to do is,

Select (ColA/ColB) as Sum1, ColB into #TempTab from SomeTable

Go

Select (Sum1/ColB) as Calc1 from #TempTab

Go

In doing so, if there other places in your Stored procedure that u need to use this value(Sum1), then its better you take it into a temp table and then refer it. The Sub query may take a longer time if the calculate value is refered more than once in the Stored procedure. Hope I have been help full.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

5409045121009's solution is a derived table, not a correlated subquery. The F(..) that looks like a function is the table alias of the derived table. It's just like the derived table waviedavie used ("inner join (...) as s2") except 5409045121009 added the column names.

I would use the 5409045121009 solution myself, although I don't personally bother adding column names to aliases. I don't know why you would use a self-join like waviedavie did.

philipino Grasshopper Points: 13 More actions · Answer 7

All the info mentioned in this chain very very useful.

But r we forgetting the fundamental problem tht was mentioned by jnelson?? Will it not be better to repeat the computation rather than use all these approaches?? What i mean is

Select (colA+ColB), (colA+colB)/ColC From SomeTable

Is the above query not going to perform better than the other suggest methods??.....

rstone SSCertifiable Points: 6213 More actions · Answer 8

The time to do most math calculations is small compared to network IO. I'd be very surprised if you could see much difference. An index on a wide table will probably have much more effect. If the expression is commonly used and complicated, maybe a computed column can be used. If you are real desperate for speed, the value can be pre-computed in a trigger.

If the query does not return results, then a local variable can be used to store the value. I tried this just to see if there was much difference (5 columns using 5 adds vs. 5 columns using 1 add). Performance improved by about 10% (206000 records in .813 seconds improved to .713 seconds). This query was done using a wide table. Adding an index using the columns in the calculation increased performance by 20% (.713 seconds to .53 seconds).

Adding network IO by returning a recordset washes out the difference on my connection.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

You have to look at the execution plans before making assumptions about the efficiency of different approaches.

Just because you repeat a calculation several times does not mean it will have to be executed twice. It does give you the opportunity to make a mistake in one of the copies that may be very hard to debug.

I like the derived table approach because I believe it is easier to read. Take a query with a complex calculation, put parenthesis around it and give it an alias, and now you can write a query around it that uses the calculated field as many times as you like; for other calculated fields, in the GROUP BY and ORDER BY clauses, etc.

I have taken fairly complicated procedural code like this: