0

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?

Loofer
  • 6,841
  • 9
  • 61
  • 102
  • 2
    Your approach using a subquery is correct. – gvee Mar 25 '15 at 12:59
  • 1
    The reason you're not allowed to refer to other columns created by the select clause is because the SQL language was specified so that all columns are computed "as if" they're all computed in parallel. – Damien_The_Unbeliever Mar 25 '15 at 13:20

2 Answers2

4

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
Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Thanks. The issue with duplicating the logic is that if someone changes it in one place they must remember to do it in the second. Is either approach beneficial? the CTE one looks clearer to my eye. – Loofer Mar 25 '15 at 13:07
  • @Loofer: yes, that's a good argument against repetition. Another one is that it's often more complex. If you look at it later you need to find out first if both are really the same. I also prefer the CTE. – Tim Schmelter Mar 25 '15 at 13:08
  • Also you removed my edit to explain what a CTE is... (Common Table Expression) was that deliberate? I did not understand the acronym until looking at the link you posted, but is there a reason you reverted that change? – Loofer Mar 25 '15 at 13:10
  • @Loofer: sorry, i also edited it at that time. I've added a link to the common-table-expression documentation. Note that you can name the cte as you will, it's just a name. So instead of `WITH CTE` you could write `WITH MyValues` – Tim Schmelter Mar 25 '15 at 13:18
1

You can name your values very simple like this:

SELECT 
  one, two, one + two as three
FROM (values(1,2)) x(one,two)
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92