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.
This question is related to
sql
sql-server
tsql
The answer is
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
Source: Stackoverflow.com