Re: Hierarchies within Database
Date: Sat, 4 Aug 2001 22:14:23 -0400
Message-ID: <3b6caa1e_3_at_news3.prserv.net>
Serge,
> Note that SQL Server 2000 also supports table functions (they call them
> parameterized views). I don't know about SQL Servers support for
recursion.
SQL Server 2000 supports user-defined functions. User-defined functions can return scalars, or they can return tables ... but SQL Server does not have anything called "parameterized views" ... however, the SQL Server 2000 Books Online does state "functions can be used to achieve the functionality of parameterized views".
I apologize if this comes across as nit-picking terminology, but I do believe in exactness when trying to respond to a question. I have encountered circumstances where inexactness of terminology has greatly complicated communication.
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.
"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message
news:3B6C9E09.8434AA49_at_ca.ibm.com...
> 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.
> -------------------
> Hi,
>
> I got asked via email how to dump a hierarchical relationship to the
> screen using
> a string to indicate the path with the position.
> Just thought the solution might be usefull for more than just that case,
> so here is a general example that can be customized.
> The script uses SQL table functions of DB2 UDB V71 to encaspulate the
> recursion.
> RANK() is used to dump the positions..
> It can be rewritten into a simple recursive query for DB2 V6.1.
>
> Cheers
> Serge
>
> create table hier(id char(8), parent char(8));
> insert into hier values
> ('1', NULL),
> ('2', NULL),
> ('11', '1'),
> ('12', '1'),
> ('111', '11'),
> ('112', '11'),
> ('113', '11'),
> ('121', '12'),
> ('122', '12'),
> ('1211', '121'),
> ('21', '2'),
> ('22', '2'),
> ('211', '21'),
> ('212', '21'),
> ('213', '21'),
> ('221', '22'),
> ('222', '22'),
> ('2211', '221');
>
> create function get_direct_childs(code varchar(30), parent char(8))
> returns table(code varchar(30), id char(8))
> reads sql data deterministic no external action
> return
> select code || '.' || rtrim(char(rank() over (order by child_id))),
> child_id
> from (select id from hier
> where hier.parent
> = get_direct_childs.parent)
> as T(child_id);
>
> create function get_rec_childs(root char(8))
> returns table(code varchar(30), id char(8))
> reads sql data deterministic no external action
> return
> with rec(code, id)
> as (values(cast('1' as varchar(30)), root)
> union all
> select t.code, t.id
> from rec, table(get_direct_childs(rec.code, rec.id)) as T)
> select code, id from rec;
>
> db2 => select * from table(get_rec_childs(char('1'))) as t order by 1;
>
> CODE ID
> ------------------------------ --------
> SQL0347W The recursive common table expression "SRIELAU.REC" may
> contain an
> infinite loop. SQLSTATE=01605
>
> 1 1
> 1.1 11
> 1.1.1 111
> 1.1.2 112
> 1.1.3 113
> 1.2 12
> 1.2.1 121
> 1.2.1.1 1211
> 1.2.2 122
>
> 9 record(s) selected with 1 warning messages printed.
>
>
Received on Sun Aug 05 2001 - 04:14:23 CEST