How to use with clause with union all in oracle sql?

@gilbert 

To use the WITH clause with UNION ALL in Oracle SQL, you can define common table expressions (CTEs) for each SELECT statement in the UNION ALL and then reference these CTEs in the main query.

Here's an example:

WITH cte1 AS ( SELECT * FROM table1 ), cte2 AS ( SELECT * FROM table2 ) SELECT * FROM cte1 UNION ALL SELECT * FROM cte2;

In this example, cte1 and cte2 are CTEs that represent the result of the SELECT statements on table1 and table2 respectively. The main query then selects all rows from cte1 and cte2 using UNION ALL to combine the results.

You can have as many CTEs as needed in the WITH clause and reference them in the main query to combine the results using UNION ALL.