SQL not recognizing column alias in where clause

Question

SQL is having an issue with the WHERE clause of this script:

SELECT
  ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY, 
  (ITEM_PRICE*QUANTITY) AS price_total, 
  (DISCOUNT_AMOUNT*QUANTITY) AS discount_total, 
  ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total
FROM ORDER_ITEMS
WHERE item_total > 500
ORDER BY item_total;

I am receiving this error:

Error starting at line : 1 in command -
SELECT 
  ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY,  
  (ITEM_PRICE*QUANTITY) AS price_total,  
  (DISCOUNT_AMOUNT*QUANTITY) AS discount_total,  
  ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total 
FROM ORDER_ITEMS 
WHERE item_total > 500 
ORDER BY item_total DESC;
Error at Command Line : 7 Column : 7
Error report -
SQL Error: ORA-00904: "ITEM_TOTAL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Why has it no issue with price_total nor discount_total, but is reporting item_total as invalid?

I am trying to first select only the items which have a total greater than 500 when the discount amount is subtracted and it is multiplied by the quantity. Then, I need to sort the results in descending order by item_total.

Your title says it all. Column aliases are not recognized in the `where` clause. That is how SQL works. Use a subquery, CTE, or repeat the expression. — Gordon Linoff, Mar 02 '15 at 03:12
also note that your error message points to the 2nd item_total (`Error at Command Line : 7 Column : 7`) and doesn't complain about same on line 5. Good luck. — shellter, Mar 02 '15 at 03:33
@GordonLinoff But it seems pretty ridiculous to me that it doesn't... why would it provide the ability to define an alias, but then not recognize that alias itself? That doesn't make any sense to me. Arguably, counterintuitive behavior is wrong behavior. — Michael, Oct 05 '21 at 19:45

score 39 · Accepted Answer · answered Mar 02 '15 at 05:51

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>

Note that in Oracle, you can't use alias' in either `GROUP BY` or `HAVING`. — max, Dec 12 '19 at 21:55
thanks for giving a reasonable explanation for why this un-intuitive behavior exists. — Michael, Oct 05 '21 at 19:47
@max thank you! So then you can only use an alias in ORDER BY? How useless. — Devyzr, Jan 26 '22 at 22:16

score 2 · Answer 2 · edited May 02 '19 at 14:06

You cannot use the column name which is used as alias one in the query

Reason:

The query will first checks for runtime at that time the column name "item_total" is not found in the table "ORDER_ITEMS" because it was give as alias which is not stored in anywhere and you are assigning that column in desired output only

Alternate:

If you want to use that type go with sub queries it's performance is not good but it is one of the alternate way

SELECT * FROM
 (SELECT
  ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY, 
  (ITEM_PRICE*QUANTITY) AS price_total, 
  (DISCOUNT_AMOUNT*QUANTITY) AS discount_total, 
  ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total
 FROM ORDER_ITEMS) as  tbl
WHERE tbl.item_total > 500
ORDER BY tbl.item_total;

Oh great. So because of wrong behavior by the language we have to settle for sub-optimal behavior? — Michael, Oct 05 '21 at 19:46

score 2 · Answer 3 · answered Sep 23 '18 at 09:48

Starting from Oracle 12c you could use CROSS APPLY to define expression and then you could refer to them in WHERE clause:

SELECT
  o.ITEM_ID, o.ITEM_PRICE, o.DISCOUNT_AMOUNT, o.QUANTITY, 
  s.price_total, s.discount_total, s.item_total
FROM ORDER_ITEMS o
CROSS APPLY (SELECT ITEM_PRICE*QUANTITY AS price_total, 
                    DISCOUNT_AMOUNT*QUANTITY AS discount_total, 
                  (ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY AS item_total FROM dual) s
WHERE s.item_total > 500
ORDER BY s.item_total;

SQL not recognizing column alias in where clause

3 Answers3

Linked

Related