Path: utzoo!attcan!uunet!aplcen!samsung!cs.utexas.edu!rutgers!deejay!yachaya!brontolo!peter
From: peter@brontolo.sublink.ORG (SINDATA Network administrator)
Newsgroups: comp.databases
Subject: Re: SQL Poser
Summary: The very hard problem of a recursive cursor....
Message-ID: <355@brontolo.sublink.ORG>
Date: 4 Jun 90 15:24:32 GMT
References: <6588@umd5.umd.edu>
Organization: SINDATA srl Unix site in Vimercate, Italy
Lines: 94

In article <6588@umd5.umd.edu>, jay@umd5.umd.edu (Jay Elvove) writes:
> A friend of mine put this question to me:
> 
> 	Given a two-column table, where column one is a primary key
> 	designating an employee and column two designates his boss, and
> 	columns one and two are identical when an employee is his/her own
> 	boss; using SQL, how would one go about displaying the chain of
> 	command of each employee in the table?  Assume each employee can
> 	only have one boss.  A simple example follows:
> 
> 
>     Given F, the chain            A
>     of command is D B A          / \
>                                 B   C
>                                /     \
>                               D       E
>                              / \
>                             F   G
> 
It is the very hard problem of the "recursive cursor" that Date told
is not already resolved, because cursors are NOT recursive.
An ugly answer may be the following (using Informix's ESQL/C) :

Assume that the table is named "company" and that column names are
"emp_name" and "boss_name".

.......

void employee_search();

main()
{

	........


	employee_search("Big Boss"); /* Assume A's name is "Big Boss" */
}
/*
	Employee_search
	search for one layer of the employees tree
*/
void employee_searc(boss_tos)
$string *boss_tos;	/* Name of the boss to search */
${
$	string emp_name[xx];	/* Employee name		*/
$	string boss_name[xx];	/* Boss name			*/
$	static string last_name[xx]; /* Last name found		*/
	short end_fl=0;

	last_name[0]='\0';	/* Reset employee name search   */

	while(!end_fl) {	/* Endless loop			*/

$		declare search_curs cursor for select
		emp_name,boss_name
		into
		$emp_name,$boss_name
		from company
		where boss_name = $boss_tos
		and   emp_name  > $last_name
		order by 2,1;

$		open search_curs;

		for(;;) {
$			fetch search_curs;
			if(sqlca.sqlcode == SQLNOTFOUND) {
$				close search_curs;
				return;
			}
			/* Here we have the employee name to handle
			   may be he or her is a boss		*/

			/* Check if the employee is also a boss */
$			select boss_name from company
			where boss_name = $emp_name;
			if( sqlca.sqlcode == 0L ) {	/* Yes, it is ! */
$				close search_curs;
				employee_search(emp_name); /* Search next layer*/
				strcpy(last_name,emp_name);/* Go back to current*/
				break;
			}
		}
	}
}

This is the answer to an "explosion" problem.
It may be easily adapted to an "implosion" one, such as the one you stated.
Peter

-- 
Peter Komanns ************- SINDATA srl -*****************
Via Rovereto 17            | BANG ..!rutgers!deejay!yachaya!brontolo!peter
20059 Vimercate MI ITALY   | SUBLINK peter@brontolo
(voice) +39-39-6083733		(fax)   +39-39-6083957