4 Replies Latest reply: Jan 23, 2012 2:05 PM by Aketi Jyuuzou RSS

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

    Aketi Jyuuzou
      http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2077142

      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;
      
      BANNER
      -------------------------------------------------------
      Oracle Database 11g Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - 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"
          BobLilly
          Hi Aketi,

          It works in 11.2.0.2, so it is probably a bug:
          select * from v$version
          
          BANNER                                                                           
          -------------------------------------------------------------------------------- 
          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production     
          PL/SQL Release 11.2.0.2.0 - Production                                           
          CORE     11.2.0.2.0     Production                                                         
          TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production                
          NLSRTL Version 11.2.0.2.0 - 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                      
          Regards,
          Bob
          • 2. Re: To use "analytic function" at "recursive with clause"
            Frank Kulash
            Hi,

            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 11.2.0.1.0.
            • 3. Re: To use "analytic function" at "recursive with clause"
              Aketi Jyuuzou
              Thanks for replys.

              When I will get 11.2.0.2.0,
              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;
              • 4. Re: To use "analytic function" at "recursive with clause"
                Aketi Jyuuzou
                I have more doubt.
                So I have created new thread at Documentation forum.
                "analytic functions" can be used at "recursive with clause" or not