SQL Not Recognizing Column Alias in Where Clause

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:

1. Sub-query
2. 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
     ;

Using an Alias in a WHERE clause

This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.

You can do a sub-select and filter on it:

SELECT * FROM
(
  SELECT A.identifier
    , A.name
    , TO_NUMBER(DECODE( A.month_no
      , 1, 200803 
      , 2, 200804 
      , 3, 200805 
      , 4, 200806 
      , 5, 200807 
      , 6, 200808 
      , 7, 200809 
      , 8, 200810 
      , 9, 200811 
      , 10, 200812 
      , 11, 200701 
      , 12, 200702
      , NULL)) as MONTH_NO
    , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
  FROM table_a A
    , table_b B
  WHERE A.identifier = B.identifier
) AS inner_table
WHERE 
  MONTH_NO > UPD_DATE

Interesting bit of info moved up from the comments:

There should be no performance hit.
Oracle does not need to materialize
inner queries before applying outer
conditions -- Oracle will consider
transforming this query internally and
push the predicate down into the inner
query and will do so if it is cost
effective. – Justin Cave

Why can't I use my column alias in WHERE clause?

You have several issues with your query:

• The filtering conditions should be in the outer query.
• The new column definition should be in the inner query.
• The order by should be in the outer query.

With these changes, it should work fine:

SELECT ID, NUMBER_OF_PEOPLE, PREV_NUMBER_OF_PEOPLE, DATE
FROM (SELECT D.*,
             LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
      FROM DATAFRAME D
     ) AS InnerQuery
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE AND
      DATE >= CURRENT_DATE - 90
ORDER BY DATE DESC;

You need the filtering after the LAG() so you can include the earliest day in the date range. If you filter in the inner query, the LAG() will return NULL in that case.

You need to define the alias in the subquery so you can refer to it in the WHERE. Aliases defined in a SELECT cannot be used in the corresponding WHERE. This is a SQL rule, not due to the database you are using.

Column alias not recognized in WHERE-statement

Use HAVING

HAVING
  LatestBookableTimestamp < UNIX_TIMESTAMP()

On a side note, you're using a dependednt subquery, which is a bad idea performance wise.

Try like this:

SELECT 
  a.ID,
  a.DistanceFromUtrecht,
  pp.LatestBookableTimestamp
FROM
  Accommodation AS a
INNER JOIN (
  SELECT
    FK_Accommodation,
    MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp
  FROM 
    PricePeriod 
  GROUP BY 
    FK_Accommodation
) AS pp    
ON pp.FK_Accommodation = a.ID    
WHERE
  pp.LatestBookableTimestamp < UNIX_TIMESTAMP()

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.

Using column alias in WHERE clause of MySQL query produces an error

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to
refer to a column alias in a WHERE
clause. This restriction is imposed
because when the WHERE code is
executed, the column value may not yet
be determined.

Copied from MySQL documentation

As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this question too: WHERE vs HAVING.

How to use alias column name in where clause in SQL Server

You can't use aliased columns in a WHERE clause. You can try using a derived table. Perhaps something like this (sorry, not tested):

SELECT * FROM
(SELECT SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
 POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 
 AS DistanceFromAddress from tblProgram) mytable
WHERE DistanceFromAddress < 2

Column not recognized in select statement

You can't refer to an alias defined in the SELECT clause in the same clause (nor in the WHERE clause for example). You need to either repeat the original expression, or use a derived table (subquery or cte).

Here the expression is simple enough so repeating seems more relevant:

SELECT 
    t.Name, 
    t.UnitPrice, 
    COUNT(*) AS Purchase_count, 
    COUNT(*) * t.UnitPrice AS Total_per_track
FROM Track t
INNER JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY t.TrackId, t.Name, t.UnitPrice
ORDER BY Total_per_track desc
LIMIT 10;

Notes:

• I added TrackId and UnitPrice to the GROUP BY clause; TrackId is there to avoid wrongly grouping together two tracks that would have the same Name; UnitPrice appears in the SELECT clause and is not part of an aggregate function, so it is a good practice to have it in the GROUP BY clause too (although, it does seem to be functionally dependant on TrackId)

• table aliases make the query shorter to read and write

---

Related Topics