Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?
- by Cade Roux
Is this valid ANSI SQL?:
SELECT 1 AS X
,2 * X AS Y
,3 * Y AS Z
Because Teradata (12) can do this, as well as this (yes, crazy isn't it):
SELECT 3 * Y AS Z
,2 * X AS Y
,1 AS X
But SQL Server 2005 requires something like this:
SELECT X
,Y
,3 * Y AS Z
FROM (
SELECT X
,2 * X AS Y
FROM (
SELECT 1 AS X
) AS X
) AS Y