ORA-22818: subquery expressions not allowed here

ORA-22818: subquery expressions not allowed here error occurs when a subquery is found in a sql query where the subquery is not supported. Subqueries are not supported in various clauses, such as GROUP BY, MATERIALIZED VIEW, and so on. If the subquery is found at a location that Oracle does not support, Oracle will be unable to parse and execute it. The error message ORA-22818: subquery expressions not allowed here will be thrown by Oracle.

Some clauses, like as GROUP BY, MATERIALIZED VIEWS, and so on, are not supported for adding a subquery. If the subquery is included in those clauses of the sql query, it will cause data consistency issues in the database. Oracle will not allow you to include subqueries in certain SQL Statement clauses. The select query in the materialised view should be modified to avoid the subquery. The materialised view will be created by utilizing joins in the sql query. Another method is to use the subquery to create a regular view, and then use the normal view to create a materialised view. If Oracle finds a subquery in those clauses, the error ORA-22818: subquery expressions not allowed here will be thrown.



Problem

Subqueries are not permitted in sql clauses like as GROUP BY, MATERIALIZED VIEWS, and so on. Oracle will throw an error if a subquery is inserted in those clauses. Create a SQL statement that includes a group by clause. If you include a subquery in the group by clause, an error will be thrown. Similarly, if you create a materialised view with a subquery, an error ORA-22818: subquery expressions not allowed here will be thrown.

CREATE MATERIALIZED VIEW myemployee AS
select 
id, name,
(select name from dept where id=deptid)
from employee;


Error

The following error “ORA-22818: subquery expressions not allowed here” will be shown if a subquery is created where the subquery is not supported. The stack trace of the oracle error is provided below.

Error starting at line : 38 in command -
CREATE MATERIALIZED VIEW myemployee AS
select 
id, name,
(select name from dept where id=deptid)
from employee
Error report -
ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.


Root Cause

Oracle does not allow subqueries in some clauses of SQL queries, such as GROUP BY, MATERIALIZED VIEWS, and so on. If you include subqueries in such clauses, the sql subquery may create data inconsistency. Oracle does not support the subqueries in those clauses. The error ORA-22818: subquery expressions not allowed here will be thrown if you include subqueries in those clauses.



Solution 1

Oracle will give an error if a materialised view is generated using subqueries. Remove the subquery and rebuild the select query in the materialised view. Another method is to use the subquery to create a normal view and then use the normal view to create a materialised view.

Problem

CREATE MATERIALIZED VIEW myemployee AS
select id, name,
(select name from dept where id=deptid)
from employee;

ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.

Solution A

CREATE VIEW myview AS
select id, name,
(select name from dept where id=deptid)
from employee;

CREATE MATERIALIZED VIEW myemployee AS select * from myview;

Solution B

CREATE MATERIALIZED VIEW myemployee AS
select e.id, e.name, d.name from employee e
join dept d on d.id=e.deptid;


Solution 2

If the group by clauses uses the subquery, the subquery should be removed before executing the group by clause. The example below shows how the group by clause is created using subquery and how it is fixed in the SQL Statement. This will help to resolve the error “ORA-22818: subquery expressions not allowed here”.

Problem

select 
    CASE WHEN name = 'hr' THEN (select name from dept where id=deptid)
     ELSE 'NA'END, 
     count(name)     
from employee
GROUP BY CASE WHEN name = 'hr' THEN (select name from dept where id=deptid)
     ELSE 'NA'END;

Error

ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.
Error at Line: 14 Column: 38

Solution

select 
    CASE WHEN name = 'hr' THEN 'hr'
     ELSE 'NA'END, count(name)   
from employee
GROUP BY CASE WHEN name = 'hr' THEN 'hr'
     ELSE 'NA'END;



Leave a Reply