This discussion is archived
2 Replies Latest reply: Jan 26, 2012 4:19 AM by Aketi Jyuuzou RSS

"analytic functions" can be used at "recursive with clause" or not

Aketi Jyuuzou Oracle ACE
Currently Being Moderated
http://docs.oracle.com/cd/E14072_01/server.112/e10880/e29250.htm#ORA-32486
<i>
ORA-32486: unsupported operation in recursive branch of recursive WITH clause

Cause: The recursive component of the UNION ALL in a recursive WITH clause element used an operation that was currently not supported. The following should not be used in the recursive branch of the UNION ALL operation: GROUP BY, DISTINCT, MODEL, grouping sets, CONNECT BY, window functions, HAVING, aggregate functions.

Action: Rewrite the query without the unsupported operation.
</i>

<i>
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.
</i>

There are 2 documents.
Previous explains that "window functions" should not be used in the recursive branch.
Another explains that "analytic functions" permitted in the recursive branch.

My question is which is true ?:|

memo from To use "analytic function" at "recursive with clause"
Oracle 11.2.0.1 does not allows "analytic functions" can be used at "recursive with clause".
Oracle 11.2.0.2 allows "analytic functions" can be used at "recursive with clause".

Legend

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