Re: Q: How do you do this in SQL ?
Date: 1996/08/21
Message-ID: <321AE72A.D72_at_scn.de>
Hi Satish,
I have solved the problem with SPL using recursive calls:
CREATE PROCEDURE expand_tree1(start_here INTEGER)
RETURNING INTEGER,CHAR(1),CHAR(15),INTEGER,INTEGER;
DEFINE m_id INTEGER;
DEFINE m_kc CHAR(1);
DEFINE m_name CHAR(15);
DEFINE m_parent INTEGER;
DEFINE m_lev INTEGER;
FOREACH WITH HOLD
SELECT id,kc,name,parent
INTO m_id,m_kc,m_name,m_parent
FROM keywordclass
WHERE id = start_here
RETURN m_id,m_kc,m_name,m_parent,0
WITH RESUME;
FOREACH EXECUTE PROCEDURE expand_tree2(m_id,1)
INTO m_id,m_kc,m_name,m_parent,m_lev
RETURN m_id,m_kc,m_name,m_parent,m_lev
WITH RESUME;
END FOREACH;
END FOREACH;
END PROCEDURE;
CREATE PROCEDURE expand_tree2(start_here INTEGER,
lev INTEGER) RETURNING
INTEGER,CHAR(1),CHAR(15),INTEGER,INTEGER;
DEFINE m_id INTEGER;
DEFINE m_kc CHAR(1);
DEFINE m_name CHAR(15);
DEFINE m_parent INTEGER;
DEFINE m_lev INTEGER;
FOREACH WITH HOLD
SELECT id,kc,name,parent
INTO m_id,m_kc,m_name,m_parent
FROM keywordclass
WHERE parent = start_here
RETURN m_id,m_kc,m_name,m_parent,lev
WITH RESUME;
FOREACH EXECUTE PROCEDURE expand_tree2(m_id,lev+1)
INTO m_id,m_kc,m_name,m_parent,m_lev
RETURN m_id,m_kc,m_name,m_parent,m_lev
WITH RESUME;
END FOREACH;
END FOREACH;
END PROCEDURE;
The first procedure queries all rows with the starting point, then
invokes the second stored procedure with the returned id as parent.
The 2nd procedure now calls itself until the tree is completely
expanded.
I wrote this proc for another table with a similar structure than yours and changed the names to fit for your needs, I hope I made not typos.
Sincerely,
Roland
Satish Katiyar wrote:
>
> I should know SQL better but for now can you please help me ?
>
> I have a table
>
> create table keywordclass (
> Id integer,
> KC char(1), // keyword or class ?
> Name char(15),
> Parent integer // refers to Id
> );
>
> It stores keyword hierarchies. Sample data is
>
> 1, 'C', 'Living', 1
> 2, 'C', 'Animal', 1
> 3, 'C', 'Plant', 1
> 4, 'K', 'Tiger', 2
> 5, 'C', 'Bird', 2
> 6, 'K', 'Parrot', 5
>
> How do you write a query in SQL that will retrieve the full
> keyword hierarchy (tree) ? (Assuming that names are unique).
> For example, retrieve the tree for "Animal", should return
>
> 2, 'C', 'Animal', 1
> 4, 'K', 'Tiger', 2
> 5, 'C', 'Bird', 2
> 6, 'K', 'Parrot', 5
>
> What will be the query if keywords and class names could be
> the same and duplicate ?
>
> Thanks a lot for your time. I would appreciate if you could
> send your reply as email.
>
> Satish
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Satish Katiyar Email : katiyar_at_informix.com
> Informix Software, Inc. Work : (510) 873 - 8494
> 1111 Broadway, Suite 2000 Fax : (510) 873 - 6275
> Oakland, California 94607 Home : (510) 769 - 6953
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Received on Wed Aug 21 1996 - 00:00:00 CEST