I would like to write a statement that looks like this
select 1 as one, 2 as two , one + two as three
However SQL Server cannot work out what one + two is.
I can re-write in this form
SELECT x.one
,x.two
,x.one + x.two AS three
FROM (
SELECT 1 AS one
,2 AS two
) x
Which gives me the output I expect. It is just a bit messy (and more so in a non-contrived example) I am also not sure what this sort of thing does to the internals of SQL Server and what implications there are to speed of execution.
Is there a better way of doing this?
You cannot refer to an alias in the same SELECT, you need to define it in a sub-query(like you did) or in a Common-table-expression(CTE):
WITH CTE AS
(
SELECT 1 as one, 2 as two
)
SELECT one, two, one + two AS three FROM CTE
Or with this syntax:
WITH CTE(one, two) AS
(
SELECT 1, 2
)
SELECT one, two, one + two as three from CTE
The same rule applies to the WHERE: Reference alias (calculated in SELECT) in WHERE clause
But normally it doesn't hurt if you use the same expression multiple time, the sql server optimizer will evaluate it only once. So you could do:
SELECT 1 as one, 2 as two , 1 + 2 as three
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments