Referencing Column Aliases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Referencing Column Aliases

Author

Topic

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-09 : 15:47:11

I am converting an Access database to SQL Server. I am trying to rewrite a query, and I need some syntax assistance.

For example, I have the following:
Select [a] + [b] as y,
y * [x] as z
From Table

How do I reference the y result so that it can be used again? Access made it easy, and some of these calculations are very, very long so this is just a simplified version.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-09 : 16:02:49

You could type it in long hand and hope that the SQL Engine could figure it out or..... You could put the complex part into a CTE and do the simple parts in the resultset that is based on the CTE. To use your simple example:[CODE];with cteMyTable as (
select [a] + [b] as y, x
from MyTable
)
select y, y * x as z
from cteMyTable[/CODE]
===============================================================================
“Opportunity is missed by most people because it is dressed in overalls and looks like work.”
-T.A.Edison

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-09 : 16:16:42

Let me get this straight... if I have a lot of these types of y results coming from the same Table, lets call it TableA

I can do the following?
;with cteMyTable as (
select [a] + [b] as y, x, [c]^2 as d, [e]^3+[f]^2 as g
from TableA
)
select y, y * x as z, d, g, d/g as n
from cteMyTable

If I have joins from multiple tables, that is on the TableA portion of this query, correct? What is the acronym for CTE?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 16:54:16

>>What is the acronym for CTE
CTE is the acronym

common table expression

Be One with the Optimizer
TG

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-05-10 : 00:12:14

Why would the result of this be 0? It appears it is being converted to integer even if I use the Cast function.

Select
(1/3) as x
From MyTable

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-11 : 22:12:23

if you convert the denominator (3) to something more precise than integer then you'll get a more accurate result.
ie: select 1/3.0

Be One with the Optimizer
TG

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-13 : 09:43:02

quote:
Originally posted by DatabaseStudent

I am converting an Access database to SQL Server. I am trying to rewrite a query, and I need some syntax assistance.

For example, I have the following:
Select [a] + [b] as y,
y * [x] as z
From Table

How do I reference the y result so that it can be used again? Access made it easy, and some of these calculations are very, very long so this is just a simplified version.

Here's one way using CROSS APPLY:


select y, z
from Mytable
cross apply (select [a] + [b] as y) y
cross apply (select y*x as z) z

Cross apply basically says, "do this for every row". You can use table-valued function calls or subqueries. In this case, they just do the calculations and alias the results, which are then returned in the main select. It's a little shorter than the CTE version.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources