SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Invoices WHERE BalanceDue > 0 --error The calculated value BalanceDue that is set as a variable in the list of selected columns cannot be used in the WHERE clause.
Is there a way that it can? In this related question (Using a variable in MySQL Select Statment in a Where Clause), it seems like the answer would be, actually, no, you would just write out the calculation (and perform that calculation in the query) twice, none of which is satisfactory.
5 Answers
You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:
SELECT BalanceDue FROM ( SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Invoices ) AS x WHERE BalanceDue > 0; Or just repeat the expression:
SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Invoices WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0; I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.
PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.
Here are 5 example queries that all yield the exact same execution plan:
SELECT LEN(name) + column_id AS x FROM sys.all_columns WHERE LEN(name) + column_id > 30; SELECT x FROM ( SELECT LEN(name) + column_id AS x FROM sys.all_columns ) AS x WHERE x > 30; SELECT LEN(name) + column_id AS x FROM sys.all_columns WHERE column_id + LEN(name) > 30; SELECT name, column_id, x FROM ( SELECT name, column_id, LEN(name) + column_id AS x FROM sys.all_columns ) AS x WHERE x > 30; SELECT name, column_id, x FROM ( SELECT name, column_id, LEN(name) + column_id AS x FROM sys.all_columns ) AS x WHERE LEN(name) + column_id > 30; Resulting plan for all five queries:
You can do this using cross apply
SELECT c.BalanceDue AS BalanceDue FROM Invoices cross apply (select (InvoiceTotal - PaymentTotal - CreditTotal) as BalanceDue) as c WHERE c.BalanceDue > 0; It's actually possible to effectively define a variable that can be used in both the SELECT, WHERE and other clauses.
A cross join doesn't necessarily allow for appropriate binding to the referenced table columns, however OUTER APPLY does - and treats nulls more transparently.
SELECT vars.BalanceDue FROM Entity e OUTER APPLY ( SELECT -- variables BalanceDue = e.EntityTypeId, Variable2 = ...some..long..complex..expression..etc... ) vars WHERE vars.BalanceDue > 0 Kudos to Syed Mehroz Alam.
As a workaround to force the evaluation of the SELECT clause before the WHERE clause, you could put the former in a sub-query while the latter remains in the main query:
SELECT * FROM ( SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Invoices) AS temp WHERE BalanceDue > 0 A simple approach
SELECT * FROM Invoices GROUP BY id HAVING(InvoiceTotal - PaymentTotal - CreditTotal) > 0 
