RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Avoid Transact-SQL's Column Alias Limitations : Page 2

A column alias seems like a great tool for referencing complex expressions, but SQL Server doesn't work that way. Learn a simple workaround.


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 column—it's held in memory. Technically, a derived table is the result of using a SELECT statement in another SELECT statement's FROM 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 data—any 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:

  1. Retrieve all the data into a derived table.
  2. Let the main SELECT process the data (filter, group, sort, and so on).

Derived Solution

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.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date