I am only a beginner in SQL, but I've come across this annoying error. 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:
I have no idea why it has 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
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:
You could refer to the column alias in WHERE clause in the following cases:
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>