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

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.

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.

A clever alternative to the CTEs - I'll keep that in mind. Unfortunately, Teradata supports neither stacked CTEs (I believe it might support one, so that's on my list to investigate) nor the APPLY operator. – Cade Roux Mar 17 '10 at 2:43.

It doesn't need to be that ugly in SQL Server 2005+. That's why Microsoft introduced CTEs: WITH T1 AS (SELECT 1 AS X), T2 AS (SELECT X, 2 * X AS Y FROM T1) SELECT X, Y, 3 * Y AS Z FROM T2 Or you could use CROSS APPLY as Rob demonstrates - that may or may not work for you depending on the specifics of the query. I admit that it's not as clean as Teradata's, but it's not nearly as bad as the subquery version, and the original Teradata example in your question is definitely not part of the SQL-92 standard.

I'd also add that in your original example, the X, Y and Z columns are not, technically, derived columns as you call them. At least as far as Microsoft and ANSI are concerned, they are just aliases, and an alias can't refer to another alias until it actually becomes a column (i.e. Through a subquery or CTE).

We typically use multiple stacked CTEs in SQL Server. Unfortunately, they aren't allowed in Teradata ;-( I'm trying to find a middle ground. What I am finding is that Teradata performs A LOT better when you don't nest, and use this trick, but it worries me that it might not continue to work and I would have been better off leaving my CTEs converted to nesting if this breaks in Teradata in the future.

– Cade Roux Mar 17 '10 at 2:41.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.