Reference alias calculated in SELECT in WHERE clause

Question

SELECT  InvoiceTotal - PaymentTotal - CreditTotal  AS BalanceDue FROM Invoices WHERE BalanceDue  gt  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

User · Answer

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  gt  0   Kudos to Syed Mehroz Alam

User · Answer

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  gt  0

User · Answer

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  gt  0    Or just repeat the expression   SELECT  InvoiceTotal - PaymentTotal - CreditTotal  AS BalanceDue FROM Invoices WHERE   InvoiceTotal - PaymentTotal - CreditTotal    gt  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  gt  30   SELECT x FROM   SELECT LEN name    column id AS x FROM sys all columns   AS x WHERE x  gt  30   SELECT LEN name    column id AS x FROM sys all columns WHERE column id   LEN name   gt  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  gt  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  gt  30    Resulting plan for all five queries

User · Answer

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   gt  0

[sql] Reference alias (calculated in SELECT) in WHERE clause

Examples related to sql

Examples related to sql-server

Examples related to tsql