CODEWITHSUNDEEP
sqloracle-database
ambrrrgris
ambrrrgris

Reputation: 157

ORA-00904: invalid identifier issue with alias in SELECT

Oracle 11g is giving me an invalid identifier error with the following code:

SELECT idproduct, SUM(quantity) AS amnt_sold
FROM [table]
GROUP BY idproduct
HAVING amnt_sold >= 3

By deduction, the error is with the HAVING statement and the alias I've used, but I can't figure out for the life of me what the issue is, even after some researching.

Upvotes: 1

Views: 1081

Answers (1)

jarlh
jarlh

Reputation: 44696

The column alias isn't available in the HAVING clause. (Just as specified by ANSI/ISO SQL.)

Either put the SUM() in the HAVING clause:

SELECT idproduct, SUM(quantity) AS amnt_sold
FROM [table]
GROUP BY idproduct
HAVING SUM(quantity) >= 3

Or, wrap the query up in a derived table, and then use the amnt_sold column name.

SELECT idproduct, amnt_sold
FROM
(
  SELECT idproduct, SUM(quantity) AS amnt_sold
  FROM [table]
  GROUP BY idproduct
) dt
WHERE amnt_sold >= 3

Upvotes: 4

Related Questions