This discussion is archived
2 Replies Latest reply: Mar 19, 2010 12:09 AM by MichaelS RSS

with clause with union all ??

645300 Newbie
Currently Being Moderated
Hi,

I was trying to use "with" clause with union all ...but it gives me error ....
   with t1 as 
        (select '1'col1 from dual union all
         select '2' col2 from dual)select * from t1
         union 
        with t2 as 
        (select '3' col1 from dual union all
         select '4' col1 from dual )
          select * from t2
            
What wrong am i doing here ??? Thank you so much!!
  • 1. Re: with clause with union all ??
    MScallion Pro
    Currently Being Moderated
    Use the WITH clause once for all of you subqueries then add the main query;
    with t1 as
     (select '1' col1
      from dual
      union all
      select '2' col2
      from dual),
    t2 as
     (select '3' col1
      from dual
      union all
      select '4' col1
      from dual)
    select *
    from t1
    union
    select *
    from t2;
  • 2. Re: with clause with union all ??
    MichaelS Guru
    Currently Being Moderated
    Also possible:
    SQL> with t1 as 
    (
     select '1'col1 from dual union all
     select '2' col2 from dual
    )
    select * from t1
    union 
    select * from (
    with t2 as (
     select '3' col1 from dual union all
     select '4' col1 from dual
    )
    select * from t2)
    /
    COL1
    ----
    1   
    2   
    3   
    4   
    
    4 rows selected.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points