This discussion is archived
4 Replies Latest reply: Jan 23, 2012 12:05 PM by Aketi Jyuuzou RSS

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

Aketi Jyuuzou Oracle ACE
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    I have more doubt.
    So I have created new thread at Documentation forum.
    "analytic functions" can be used at "recursive with clause" or not

Legend

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