Re: Q: How do you do this in SQL ?

Roland Mueller wrote: > > 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@informix.com > > Informix Software, Inc. Work : (510) 873 - 8494 > > 1111 Broadway, Suite 2000 Fax : (510) 873 - 6275 > > Oakland, California 94607 Home : (510) 769 - 6953 > > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= I saw this in MS Sql Server Online Manual. Hope this helps. Expanding Hierarchies ===================== Databases often store hierarchical information. The following data, for example, is a hierarchical representation of regions of the world: Parent Child ------------- ------------- World Europe World North America Europe France France Paris North America United States North America Canada United States New York United States Washington New York New York City Washington Redmond This representation does not clearly show the structure implied by the data. The following example is easier to interpret: World North America Canada United States Washington Redmond New York New York City Europe France Paris The following Transact-SQL procedure expands an encoded hierarchy to any arbitrary depth. Although Transact-SQL supports recursion, it is more efficient to use a temporary table as a stack to keep track of all of the items for which processing has begun but is not complete. When processing is complete for a particular item, it is removed from the stack. New items are added to the stack as they are identified. (Note that this example is for illustration only and it does not come from the pubs database.) create proc expand (@current char(20)) as set nocount on declare @level int, @line char(20) create table #stack (item char(20), level int) insert into #stack values (@current, 1) select @level = 1 while @level > 0 begin if exists (select * from #stack where level = @level) begin select @current = item from #stack where level = @level select @line = space(@level - 1) @current print @line delete from #stack where level = @level and item = @current insert #stack select child, @level 1 from hierarchy where parent = @current if @@rowcount > 0 select @level = @level 1 end else select @level = @level - 1 end In this example, the input parameter (@current) is used to indicate theplace in the hierarchy to start. It also keeps track of the current item in the main loop. The two local variables used are @level, which keeps track of the current level in the hierarchy, and @line, which is a work area used to construct the indented line. The SET NOCOUNT ON statement avoids cluttering up the output with ROWCOUNT messages from each SELECT. The temporary table, #stack, is created and primed with the item identifier of the starting point in the hierarchy, and @level is set to match. The level column in #stack allows the same item to appear at multiple levels in the database. Although this situation does not apply to the geographic data in the example, it can apply in other examples. In this example, when @level is greater than 0, the procedure follows several steps: 1. If there are any items in the stack at the current level (@level), choose one and call it @current. 2. Indent the item @level spaces, and then print the item. 3. Delete the item from the stack so it won't be processed again, and then add all its child items to the stack at the next level (@level 1). Note With a conventional programming language, you would have to find each child item