Nested WITH Clause in Oracle AI Database 26ai(23.26.2)

The WITH clause, also known as a Common Table Expression (CTE), is widely used to make complex SQL statements easier to organize and understand. It allows a query to define an intermediate result and then reference that result from the main query.

Previously, Oracle did not support placing a WITH clause inside another WITH query block, resulting in ORA-32034. Oracle AI Database 26ai removes this restriction and supports nested WITH clauses.

This article demonstrates the enhancement by starting with a regular SQL query, rewriting it with a standard WITH clause, and finally using a nested WITH clause in Oracle AI Database 26ai.

1. Execute the Query Without a WITH Clause

Imagine we want to display each customer’s name and city, along with the total amount of their completed orders.

We can achieve this using a regular SQL statement:

SQL>SELECT c.customer_name, c.city, SUM(o.amount) AS total_amount
  FROM customers c
  JOIN orders o
    ON o.customer_id = c.customer_id
 WHERE o.status = 'COMPLETED'
 GROUP BY c.customer_name, c.city
 ORDER BY total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

2. Rewrite the Query Using a WITH Clause

Now, I want to separate the completed orders from the rest of the query.

The purpose of this query is still to display each customer’s name and city, along with the total amount of their completed orders. However, this time, the completed orders are first defined in a CTE called completed_orders.

In Oracle Database 19c, we could execute the following query:

SQL> WITH completed_orders AS
 (SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED')
SELECT c.customer_name, c.city, SUM(o.amount) AS total_amount
  FROM customers c
  JOIN completed_orders o
    ON o.customer_id = c.customer_id
 GROUP BY c.customer_name, c.city
 ORDER BY total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

3. Use a Nested WITH Clause

Now, I will change the structure of the previous query.

The purpose of this query is again to display each customer’s name and city, along with the total amount of their completed orders. The difference is that the completed_orders CTE is now defined inside another CTE called customer_totals.

In Oracle AI Database 26ai (23.26.2), we can execute:

SQL> WITH customer_totals AS
 (WITH completed_orders AS
     (SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED')
    SELECT customer_id, SUM(amount) AS total_amount
      FROM completed_orders
     GROUP BY customer_id)
SELECT c.customer_name, c.city, t.total_amount
  FROM customers c
  JOIN customer_totals t
    ON t.customer_id = c.customer_id
 ORDER BY t.total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

The nested form shown above was not executable in previous Oracle Database releases. For example, consider Oracle Database 21c (21.16.0.0.0).

If we execute the same nested query:

SQL> WITH customer_totals AS
 (WITH completed_orders AS
     (SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED')
    SELECT customer_id, SUM(amount) AS total_amount
      FROM completed_orders
     GROUP BY customer_id)
SELECT c.customer_name, c.city, t.total_amount
  FROM customers c
  JOIN customer_totals t
    ON t.customer_id = c.customer_id
 ORDER BY t.total_amount DESC;

ERROR at line 2:
ORA-32034: unsupported use of WITH clause

4. Rewrite the Query Using EXISTS

Although nested WITH clauses were not supported in previous Oracle Database releases, the same result can be achieved using EXISTS and regular subqueries.

For example, the nested WITH query can be rewritten as follows:

SELECT c.customer_name,
       c.city,
       (SELECT SUM(o.amount)
          FROM orders o
         WHERE o.customer_id = c.customer_id
           AND o.status = 'COMPLETED') AS total_amount
  FROM customers c
 WHERE EXISTS (SELECT 1
          FROM orders o
         WHERE o.customer_id = c.customer_id
           AND o.status = 'COMPLETED')
 ORDER BY total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275