WITH Clause and Recursive Queries

13.1 Subquery Factoring

Hints: /*+ MATERIALIZE */ (forces a temp table) and /*+ INLINE */.

WITH dept_costs AS (
    SELECT d.department_name, SUM(e.salary) dept_total
    FROM employees e JOIN departments d ON e.department_id = d.department_id
    GROUP BY d.department_name),
avg_cost AS (SELECT AVG(dept_total) avg_total FROM dept_costs)
SELECT * FROM dept_costs WHERE dept_total > (SELECT avg_total FROM avg_cost);

13.2 PL/SQL Functions in the WITH Clause (12c)

WITH FUNCTION tax(p NUMBER) RETURN NUMBER IS BEGIN RETURN p * 0.18; END;
SELECT product_name, unit_price, tax(unit_price) FROM products;
/

13.3 Recursive WITH (11gR2+)

WITH org (employee_id, last_name, manager_id, lvl, path) AS (
    SELECT employee_id, last_name, manager_id, 1, last_name FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.last_name, e.manager_id, o.lvl + 1, o.path || ' > ' || e.last_name
    FROM employees e JOIN org o ON e.manager_id = o.employee_id
)
SEARCH DEPTH FIRST BY last_name SET order_seq
CYCLE employee_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT LPAD(' ', 2 * (lvl - 1)) || last_name AS org_chart, path FROM org ORDER BY order_seq;

13.4 Row Generators

SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 100;
SELECT DATE '2025-01-01' + LEVEL - 1 AS d FROM dual CONNECT BY LEVEL <= 365;