4 Replies Latest reply on Jan 23, 2012 8:05 PM by Aketi Jyuuzou

    To use "analytic function" at "recursive with clause"

    Aketi Jyuuzou

      The recursive member cannot contain any of the following elements:
      ・An aggregate function. However, analytic functions are permitted in the select list.

      OK I will use analytic function at The recursive member :-)
      SQL> select * from v$version;
      Oracle Database 11g Release - Production
      PL/SQL Release - Production
      CORE      Production
      TNS for 32-bit Windows: Version - Production
      NLSRTL Version - Production
      SQL> with rec(Val,TotalRecCnt) as(
        2  select 1,1 from dual
        3  union all
        4  select Val+1,count(*) over()
        5    from rec
        6   where Val+1 <= 5)
        7  select * from rec;
      select * from rec
      ERROR at line 7:
      ORA-32486: unsupported operation in recursive branch of recursive WITH clause
      Why ORA-32486 happen ?:|
        • 1. Re: To use "analytic function" at "recursive with clause"
          Hi Aketi,

          It works in, so it is probably a bug:
          select * from v$version
          Oracle Database 11g Enterprise Edition Release - 64bit Production     
          PL/SQL Release - Production                                           
          CORE     Production                                                         
          TNS for IBM/AIX RISC System/6000: Version - Production                
          NLSRTL Version - Production                                           
          with rec(Val,TotalRecCnt) as(
          select 1,1 from dual
          union all
          select Val+1,count(*) over()
          from rec
          where Val+1 <= 5)
          select * from rec
          VAL                    TOTALRECCNT            
          ---------------------- ---------------------- 
          1                      1                      
          2                      1                      
          3                      1                      
          4                      1                      
          5                      1                      
          • 2. Re: To use "analytic function" at "recursive with clause"
            Frank Kulash

            It looks like a bug. I tried several other analytic fucntions, and various combinations of PARTITION BY and ORDER BY clauses, and got the same ORA-32486 as you did every time.
            Like you, I used Oracle
            1 person found this helpful
            • 3. Re: To use "analytic function" at "recursive with clause"
              Aketi Jyuuzou
              Thanks for replys.

              When I will get,
              I will use below "recursive with clause" which is stopping at NodeCnt.
              create table Edakiri(ID) as
              select RowNum from dict where RowNum <= 10;
              with rec(RootID,ID,Path,NodeCnt) as(
              select ID,ID,to_char(ID),count(*) over()
                from Edakiri
               where ID <= 3
              union all
              select a.RootID,b.ID,a.Path || to_char(b.ID),
              a.nodeSum+count(*) over()
                from rec a,Edakiri b
               where a.ID+1=b.ID
                 and a.NodeCnt <= 10)
              select * from rec;