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_i...