Reference alias (calculated in SELECT) in WHERE clause
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:
Reference alias in WHERE clause
This is not possible as in sql, the order of execution is first, the where clause and then the select. At the time where clause is getting executed, it does not know what you have defined as an alias and so you will get that error.
You need to rewrite your query like this..
SELECT
SUBSTRING(pk, 6, 2)::INT AS _year
FROM
listing
WHERE
SUBSTRING(pk, 6, 2)::INT > 90
How do I use alias in where clause?
The SQL-Server docs says:
column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.
Similar in the MySQL doc it says:
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
In MySQL you can at least reuse aliases in the SELECT clause
Referring to a Column Alias in a WHERE Clause
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)
Normally you can't refer to field aliases in the WHERE
clause. (Think of it as the entire SELECT
including aliases, is applied after the WHERE
clause.)
But, as mentioned in other answers, you can force SQL to treat SELECT
to be handled before the WHERE
clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):
Parenthesis/Subselect:
SELECT
*
FROM
(
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
) as innerTable
WHERE daysdiff > 120
Or see Adam's answer for a CTE version of the same.
Referencing a column alias in the WHERE clause
I would recommend writing the WHERE
as:
WHERE fila_mailing = 2638 AND
F1STA ='ANSWER' AND
CLASSE1 IN ('VC2', 'VC3') AND
FONE1 LIKE '31%' AND
LASTCALL >= '2020-10-02' AND
LASTCALL < '2020-10-31'
Note the changes to the logic:
FONE1
appears to be a string, so the comparison uses string operations.- The
DATETIME
comparisons uses>=
and<
rather thanBETWEEN
so the last second on the last day is not missed. - The date format is simplified.
SQL not recognizing column alias in where clause
An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING
clauses to refer to the column.Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is
evaluated, the column value may not yet have been determined.
So, the following query is illegal:
SQL> SELECT empno AS employee, deptno AS department, sal AS salary
2 FROM emp
3 WHERE employee = 7369;
WHERE employee = 7369
*
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier
SQL>
The column alias is allowed in:
- GROUP BY
- ORDER BY
- HAVING
You could refer to the column alias in WHERE clause in the following cases:
- Sub-query
- Common Table Expression(CTE)
For example,
SQL> SELECT * FROM
2 (
3 SELECT empno AS employee, deptno AS department, sal AS salary
4 FROM emp
5 )
6 WHERE employee = 7369;
EMPLOYEE DEPARTMENT SALARY
---------- ---------- ----------
7369 20 800
SQL> WITH DATA AS(
2 SELECT empno AS employee, deptno AS department, sal AS salary
3 FROM emp
4 )
5 SELECT * FROM DATA
6 WHERE employee = 7369;
EMPLOYEE DEPARTMENT SALARY
---------- ---------- ----------
7369 20 800
SQL>
not recognizing alias in WHERE clause
The column aliases (here: error_percentage
) are not accessible before the construction of the result set. ORDER BY
is not affected as sorting happens (necessarily) _after_the result set has been retrieved.
Use
SELECT agg.*
FROM (
SELECT date, ((CAST(error AS float) / CAST(success AS float)) *100) AS error_percentage
FROM daily_report
ORDER BY error_percentage DESC
) agg
WHERE error_percentage > 1
;
It's more efficient and probably cleaner if you trim the result set first:
SELECT agg.*
FROM (
SELECT date, ((CAST(error AS float) / CAST(success AS float)) *100) AS error_percentage
FROM daily_report
) agg
WHERE error_percentage > 1
ORDER BY error_percentage DESC
;
Reference an alias elsewhere in the SELECT list
You can't refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:
SELECT
FirstName, LastName, Other,
Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
FROM
(
SELECT FirstName, LastName,
CASE WHEN LastName = 'Jones'
THEN 'N/A'
END AS Other
FROM dbo.table_name
) AS x;
How to use column alias in where clause
You cannot use table aliases in the where
clause.
Instead, just use the expressions:
WHERE _TABLE_SUFFIX BETWEEN '20191122' AND '20191202' AND
hits.transaction.transactionId IS NOT NULL AND
ARRAY_LENGTH(hits.product) > 2 AND
associated.productSKU <> ref.productSKU
Related Topics
Delete SQL Rows Where Ids Do Not Have a Match from Another Table
Are a Case Statement and a Decode Equivalent
Insert Picture into SQL Server 2005 Image Field Using Only SQL
SQL (Oracle): Order by and Limit
Permanently Set Postgresql Schema Path
Composite Primary Key VS Additional "Id" Column
What's the Difference Between "Where" Clause and "On" Clause When Table Left Join
Join Multiple Tables with Active Records
Eliminating Duplicate Values Based on Only One Column of the Table
SQL Query to Find Nth Highest Salary from a Salary Table
Why Do People Hate SQL Cursors So Much
What Happens to an Uncommitted Transaction When the Connection Is Closed
How to Calculate a Running Total in SQL Without Using a Cursor
Storing Sex (Gender) in Database
SQL - How to Select a Row Having a Column with Max Value