Reference alias (calculated in SELECT) in WHERE clause

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 (https://stackoverflow.com/q/3567863/264031), 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.

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