Path: utzoo!dciem!nrcaer!sce!cognos!garyp
From: garyp@cognos.UUCP (Gary Puckering)
Newsgroups: comp.databases
Subject: Re: SQL Poser
Message-ID: <8514@cognos.UUCP>
Date: 20 Jun 90 15:19:24 GMT
References: <6588@umd5.umd.edu> <355@brontolo.sublink.ORG>
Reply-To: garyp@cognos.UUCP (Gary Puckering)
Organization: Cognos Inc., Ottawa, Canada
Lines: 95

In article <355@brontolo.sublink.ORG> peter@brontolo.sublink.ORG (SINDATA Network administrator) writes:
>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".

A more beautiful answer to this problem can be found in PowerHouse
StarBase (from Cognos) and in InterBase (from Interbase Software
Corporation).  You can code requests that will be instantiated each
time a recursive procedure calls itself.  The above problem requires
can be handled with the following C program:

     main ()
     {
     int emp_no;
     
     printf ("\t\tEmployee Roster\n\n");

     FOR e IN employees WITH e.boss MISSING
         printf ("%s %s\n",e.last_name,e.first_name);
         print_next (0, e.emp_no);
     END_FOR;

     EXEC SQL COMMIT RELEASE;
     }
         
     static  print_next (lvl, boss_no)
     short   lvl;
     int     boss_no;
     {
     char    dots[40];
     int     i;
     for (i=0; i<lvl; i++) dots[i]='.';
     dots[i]='\0';
     
     FOR (LEVEL lvl) e IN employees WITH e.boss = boss_no
         printf ("%s%s %s\n",template,e.last_name,e.first_name);
         print_next (lvl + 1, e.emp_no);
     END_FOR;
     }

Sample output:

                     Employee Roster
     
     Saluki Dimitri
     Chow Maudloop
     .Shepard Harold
     ..Basset Hermione
     ..Griffon Ronald
     .Setter Brenda
     ..Collie Alan
     ..Dalmation Edwin
     Bluetick Walter
     .Dachshund Emily
     
The FOR construct is part of a relational sublanguage that can be
intermixed with SQL.  Among other things it:

    *  sets up a cursor without explicit declaration
    *  sets up a looping construct in the host language
    *  sets up the termination condition for the loop
    *  automatically defines host-language variables of the correct
       type and size

The FOR construct also provides for passing in a request
instantiation level number (the value provided after the LEVEL
keyword).  Essentially, this allows a new "cursor" to be opened each
time the level number changes.
-- 
Gary Puckering                             Cognos Incorporated
  VOICE: (613) 738-1338 x6100              P.O. Box 9707
  UUCP:  uunet!mitel!sce!cognos!garyp      Ottawa, Ontario
  INET:  garyp%cognos.uucp@uunet.uu.net    CANADA  K1G 3Z4