CODEWITHSUNDEEP
sqloracle-database
tgbrunet
tgbrunet

Reputation: 260

Creating View in Oracle SQL

I am trying to create a view on my database but I keep getting ORA-00934:

group function is not allowed here error

Any help?

Here is my code:

CREATE VIEW MY_PRODUCTS_CE2_SELECT_MBILI
AS SELECT PROD_NAME,PROD_CATEGORY,PROD_SUBCATEGORY 
FROM MY_PRODUCTS_CE2
WHERE PROD_LIST_PRICE > AVG(PROD_LIST_PRICE)    
WITH CHECK OPTION CONSTRAINT MyProductsCE2SelectMbili_Cnst;

Upvotes: 0

Views: 7606

Answers (1)

Iswanto San
Iswanto San

Reputation: 18569

You can't put aggregate function in WHERE clause.

Change your query to this:

CREATE VIEW MY_PRODUCTS_CE2_SELECT_MBILI 
AS SELECT PROD_NAME,PROD_CATEGORY,PROD_SUBCATEGORY 
FROM MY_PRODUCTS_CE2 WHERE PROD_LIST_PRICE > (SELECT AVG(PROD_LIST_PRICE) FROM MY_PRODUCTS_CE2)
WITH CHECK OPTION CONSTRAINT MyProductsCE2SelectMbili_Cnst;

More : Oracle/PLSQL: ORA-00934 Error

Upvotes: 5

Related Questions