Use column aliases directly in SELECT clause
It would be nice if queries like this were possible:
A AS B
,C AS D
,D-B AS E
In other words, use column directly in the SELECT clause where they are defined. Other database vendors have implemented this, so I assume it's not rocket science.
Right now, we have to use subqueries just to define an extra calculation.
Steven Hibble commented
I agree with Erland. This will break some existing code and creates problems when columns and aliases conflict.
The CROSS APPLY method is best. When that doesn't work (e.g. window functions), a CTE or subquery suffices.
@Thomas, CROSS APPLY does not always help, for instance with windowed functions. Also it makes you constantly scroll up and down in code around the FROM statement. Vote up, Koen
Thomas Franz commented
just use a CROSS APPLY:
SELECT calc.B, calc.D, calc.D - calc.B AS E
CROSS APPLY (SELECT A AS B, C AS D) AS calc
This would also prevent you from writing the same code / formular multiple times, as you can join, filter or order e.g. by calc.D
Erland Sommarskog commented
There are products that have this behaviour, for instance Access. However, it is in direct conflict with how the SQL standard is defined, which is based on the principle as "all-at-once". Thus, all aliases in the SELECT list are defined at once. Note also that it would be a breaking change. Consider a table which has the columns a, b, c. Consider next the query:
SELECT a AS b, c + b FROM tbl
This would save me a lot of CTEs (common tables expressions) when I am doing string processing and having to repeatedly re-use CHARINDEX, etc., results.