Re: Hierarchies within Database

On Sat, 04 Aug 2001 21:14:49 -0400, Serge Rielau <srielau_at_ca.ibm.com> wrote:

>Oracle supports CONNECT BY for recursion. I don't think they have table
>functions.

Oracle does have a feature called table functions, although the code in your example looks more like what Oracle refers to as a REF CURSOR, and as you say the way to do the hierarchical lookup is through a CONNECT BY clause.

These days (Oracle9i), table functions are used more for setting up the equivalent of Unix pipelines -- passing sets of data through multiple functions (for cleansing etc.) without storing the result set between each stage.

>Hi Anthony,
>
>Praise to google!.... I was affraid I have to redo that again, here is a
>solution I posted to a similar problem: "How to remember the path in a
>recursion"
>
>To get a decent sorting you'll need to have upper limits for the number of
>siblings on a given level and fill with leading zeros (denser encodings can be
>thought of e.g. 0...Z for a base of 36 instead of 10 per digit):
>00001.00010. and so on. You are limited in DB2 by a total of 32K rowsize for the
>size of the VARCHAR holding the path.
>
>FYI, I'll write a technote for future reference, no desire to hunt that one down
>again :-)
>
>Note that SQL Server 2000 also supports table functions (they call them
>parameterized views). I don't know about SQL Servers support for recursion.
>
>Oracle supports CONNECT BY for recursion. I don't think they have table
>functions.
>
>Informix has a "bladelette" that does this kind of recursive numbering.
>It's quite neat, but I forgot how it works.
>-------------------
[example chopped]

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/