With clause not working with union

Question

My query result is a union of several queries. I am facing the below error when I use WITH clause within a union. Any ideas why?

select column1 from TABLE_A
union
with abcd as (select * from TABLE_B)
    select column2 from TABLE_A A, abcd 
    where abcd.m_reference = A.m_reference

ORA-32034: unsupported use of WITH clause
32034. 00000 - "unsupported use of WITH clause"
*Cause: Inproper use of WITH clause because one of the following two reasons
1. nesting of WITH clause within WITH clause not supported yet
2. For a set query, WITH clause can't be specified for a branch.
3. WITH clause can't sepecified within parentheses.
*Action: correct query and retry

Answer 1

Just define the CTE first, before the actual UNION query. Then use it as you would a regular table:

with abcd as (select * from TABLE_B)
select column1 from TABLE_A
union
select column2
from TABLE_A A
inner join abcd
    on abcd.m_reference = A.m_reference

You can use multiple CTE as follows:

with cte1 AS (...),
     cte2 AS (...)
select * from ...

Answer 2

Encapsulate your WITH statement in a dummy select.

select column1 from TABLE_A
union
select * from (
  with abcd as (select * from TABLE_B)
    select column2 from TABLE_A A, abcd 
    where abcd.m_reference = A.m_reference
)

Answer 3

Encapsulating it is the way to go if you have multiple WITHs; for example I just had to do this monstrosity to quickly pull in data from ID numbers from an Excel sheet

select * from (
   with childvendor as (
    select vendornumber, name From vendor where vendornumber = '0000800727'
   )
   select 
    v.vendornumber as parentvendor, 
    v.name as parentname,
    cv.vendornumber as childvendor,
    cv.name as childname
   From 
    vendor v, childvendor cv
   where 
    v.vendornumber = '0000800004'
) 
UNION ALL
select * from (
   with childvendor as (
    select vendornumber, name From vendor where vendornumber = '0000800042'
   )
   select 
    v.vendornumber as parentvendor, 
    v.name as parentname,
    cv.vendornumber as childvendor,
    cv.name as childname
   From 
    vendor v, childvendor cv
   where 
     v.vendornumber = '0000800035'
)

And so on