A Derived Table Solution
The solution to the column alias problem is a derived table. In this context, a derived table is similar to a derived columnit's held in memory. Technically, a derived table is the result of using a SELECT
statement in another SELECT
clause. You might know a derived table as a subquery. SQL Server successfully handles the column alias shown in Figure 3
by virtue of the derived table.
|Figure 3. Use a Derived Table to Use a Column Alias: SQL Server successfully handles the column alias by virtue of the derived table.|
Yes, you have to rethink your strategy a bit to get the results you want, but it's a small price to pay for the convenience!
The derived table dtTotalCost retrieves all the columns the query needs, including the aliased (or derived) column. The main SELECT then handles the logistics of manipulating the dataany filtering, grouping, sorting, and so on. In this way, the column alias exists in memory (in the derived table) when the main SELECT's WHERE clause gets around to referring to it.
The rule is simple, even if it doesn't appear so by glancing at the SQL:
- Retrieve all the data into a derived table.
- Let the main SELECT process the data (filter, group, sort, and so on).
SQL Server's engine processes a column alias at the end of a query. That means a column alias for a derived column
isn't available to other clauses. Instead of re-entering a complex expression, alias the column in a derived table
. That way, the alias exists when the main SELECT
gets around to referencing it.