2 Replies Latest reply: Mar 19, 2010 2:09 AM by MichaelS RSS

    with clause with union all ??

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