Select query which sums two columns and want to use the result as a criteria for the select

Question

I'm using Oracle SQL Developer.

I want to multiply the values of two columns, price and quantity, and then use the sum as a criteria. The user needs to be able to determine what the minimum value of the sum field is, and the query should return all results where the sum of those two columns is higher than their specified minimum.

My query right now looks like this:

SELECT PROD_PRICE, PROD_QUANTITY, PROD_PRICE * PROD_QUANTITY AS "PROD_AMOUNT" 
FROM PRODUCT 
WHERE PROD_AMOUNT >= &PROD_AMOUNT

The Error message I get is that "PROD_AMOUNT" is an invalid identifier.

Thank you so much!

Answer 1

You can't use alias of computed columns in where clause you need to repeat the expression in order to perform filter using computed value

SELECT PROD_PRICE,
       PROD_QUANTITY, 
       PROD_PRICE * PROD_QUANTITY AS "PROD_AMOUNT" 
FROM PRODUCT 
WHERE (PROD_PRICE * PROD_QUANTITY)>= &PROD_AMOUNT

Answer 2

any sql ex. oracle, MySQL , SQLite, SQLServer ... etc never use an alias in filer conditions (where or having)

SELECT PROD_PRICE,
   PROD_QUANTITY, 
   PROD_PRICE * PROD_QUANTITY AS "PROD_AMOUNT" 
FROM PRODUCT 
WHERE (PROD_PRICE * PROD_QUANTITY)>= &PROD_AMOUNT