Oracle / PLSQL: ORA-22818 Error Message

Learn the cause and how to resolve the ORA-22818 error message in Oracle.

Description

When you encounter an ORA-22818 error, the following error message will appear:

• ORA-22818: subquery expressions not allowed here

Cause

You tried to include a subquery in a GROUP BY clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error occurs when you try to execute a SQL statement that contains a subquery in the GROUP BY clause.

For example, if you tried to execute the following SELECT statement:

SELECT SUM(price),
CASE WHEN category = 'Vegetables' THEN
  (SELECT suppliers.supplier_name
   FROM suppliers)
ELSE
  'Not applicable'
END sname
FROM products
GROUP BY CASE WHEN category = 'Vegetables' THEN
           (SELECT suppliers.supplier_name
            FROM suppliers)
         ELSE
           'Not applicable'
         END;

You would receive the following error message:

Remove the subquery below from the GROUP BY clause (within the CASE statement) .

(SELECT suppliers.supplier_name
    FROM suppliers)

For instance you could write your query as follows:

SELECT SUM(price),
CASE WHEN category = 'Vegetables' THEN
  'Safeway'
ELSE
  'Not applicable'
END sname
FROM products
GROUP BY CASE WHEN category = 'Vegetables' THEN
           'Safeway'
         ELSE
           'Not applicable'
         END;