Reuse of Field Aliases

I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:

At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.

Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.

Example:
In Sybase I can write this:

Select
Price * Units as Cost
Cost * SalesTax as TotalTax
From Invoice
Where TotalTax > 3.5

However if i want to do this in MS SQL 2000 i have to go trough

Select

Price * Units as Cost

Price * Units * SalesTax as TotalTax

From Invoice

Where (Price * Units * SalesTax) > 3.5

In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?

Sorry: There is no direct translation of this feature.

Dave

|||

The only thing you could do is to reference the expression from as subquery or a common table expression (if you are using SQL Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de