Search code examples
sqlsql-serverteradataansi-sql

Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?

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
Solution

  • No, it's not valid ANSI. ANSI assumes that all SELECT clause items are evaluated at once.

    And I'd've written it in SQL 2005 as:

    SELECT *
FROM        (SELECT 1 AS X) X
CROSS APPLY (SELECT 2 * X AS Y) Y
CROSS APPLY (SELECT 3 * Y AS Z) Z
;