From: johnl@informix.com (Jonathan Leffler) Newsgroups: comp.databases.informix Subject: recursive functions and cursors Date: 13 Jun 1995 12:33:04 -0400 X-Informix-List-ID: >From: cpg5484@swuts.sbc.com (Sivagurn Ramanathan) >Date: Tue, 13 Jun 95 9:58:41 CDT >X-Informix-List-Id: > >bruce@tkg.com wrote: >> >> I have a table that contains a hierarchy of items where where each record >> references another record as its parent. >> >> [...] >> >> I'm trying to use a cursor in a recursive function but keep running into >> an error. The program compiles ok but returns the following error when ran. >> >> $ fglgo prog.4go >> A >> B >> Program stopped at "prog.4gl", line number 16. >> SQL statement error number -400. >> Fetch attempted on unopen cursor. >> >> # prog.4gl >> 1 database mydata >> 2 >> 3 main >> 4 call showitem(A) >> 5 end main >> 6 >> 7 function showitem(itemid) >> 8 define itemid like tableA.itemid, >> 9 childid like tableA.itemid >> 10 >> 11 display itemid >> 12 >> 13 declare child_cur cursor for >> 14 select itemid from tableA where parent = itemid >> 15 >> 16 foreach child_cur into childid >> 17 call showitem(childid) >> 18 end foreach >> 19 >> 20 end function >> >> Does anyone have a suggestion? > > Folks correct me if I am wrong. > > I think 4GL as such does not support recursion. I4GL does support recursion. What doesn't support recursion is ESQL/C. When you are not using cursors, I4GL supports recursion in ordinary functions. Reports cannot be used recursively reliably, though there is nothing in the language or compilers to stop you doing: REPORT x(...) ... OUTPUT TO REPORT x(...) ... END REPORT However, in the code above, the function showitem() is called for a first time. It opens the cursor with the FOREACH loop, fetches a row of data and calls itself. This re-opens the cursor (doing a close and then an open), and, for sake of argument, reads 2 rows and then closes the cursor, and returns to the first invocation. The cursor to which the first invocation has been closed twice, now. So the next fetch returns the error, quite correctly. Remember, cursors are like global variables -- there is only one of them, even when the function is recursive. In fact, if you look at the generated C code, you'll see that the cursors are indeed represented by global (or file static) variables. > But there is a work around > for your problem which I think is about one of the ways to get around it. > > DO NOT use the foreach .... end foreach approach. Instead open the cursor > out side of the function showitem() and inside the function have a while > loop to fetch the rows. > [sample deleted] This would not work, I'm sorry to report. To get the child data, you have to re-open the cursor. And the code given doesn't re-open the cursor. The original code did a depth-first traversal of the tree. It is quite difficult to simulate this directly in SQL. You can simulate breadth-first traversal and stuff the data into a temporary table, and then retrieve the data from the temporary table. I attach a shell archive containing an SQL-based set of scripts which do various sorts of hierarchical data analysis -- but you should be aware that some of the techniques are not pretty but are effective. Also the file "alternatives" itself contains two shell archives where alternatives are discussed. Also, you can use stored procedures recursively to achieve the required effect, so it may be easiest to adapt the code above into a stored procedure, and then use that from I4GL. Note that if you have an upper bound on the depth of the hierarchy, you can write code to simulate an array of cursors and do the recursion manually. It is rather ghastly (British understatement for perfectly revolting) but will work until the upper bound is exceeded. Finally, the data in the example program will be returned in a indeterminate order because no sort criteria are specified on the SELECT statement. If the data happens to be inserted in order, it will work -- the test data probably was inserted in order -- but after a few random updates, deletes and insertions, it will produce the data in any old order. I hope this makes it through the mailers... Yours, Jonathan Leffler (johnl@informix.com) #include : "@(#)shar.sh 1.9" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Tue Jun 13 08:57:34 PDT 1995 by johnl at Informix Software Ltd. # Files archived in this archive: # README # Makefile # alternatives # bom.sql # hier.sql # hier1.ace # hier1.sh # hier2.ace # hier2.sh # partexp1.sh # partexp2.sh # partexp3.sh # partexp4.sh # pp.1.sh # pp.sql # #-------------------- if [ -f README -a "$1" != "-c" ] then echo shar: README already exists else echo 'x - README (2039 characters)' sed -e 's/^X//' >README <<'SHAR-EOF' XExamples in Hierarchical Data Structures X======================================== X XThe accompanying shell scripts, ACE report and SQL files can be used to Xdemonstrate how to handle hierarchical data structures (eg Bill of XMaterials or Organisation Chart) in SQL. X XThe Bill of Materials (BoM) solutions (partexp1..partexp4) illustrate Xthe use of Breadth-First-Search. The Organisation Chart (OC) solution X(hier2) illustrates Depth-First-Search. Note that neither solution is Xneat and tidy, though the BoM is simply iterative whereas the OC Xsolution requires a recursively defined table structure. X XThe solutions use my program SQLCMD as an SQL command interpreter. The Xequivalent effect can more or less be achieved using either ISQL or XDBACCESS, but it is not as easy, and some cases would require the output Xfrom ISQL/DBACCESS to be reformatted to retain the data one row per Xline. (Where the command is of the form sqlcmd -d dbase -e "SQL stmt", Xuse echo "SQL stmt" | isql dbase -; where the command is of the form Xsqlcmd -d dbase -f file.sql, use isql dbase file; where the command is Xof the form sqlcmd -d dbase file1.sql file2.sql, use cat file1.sql Xfile2.sql | isql dbase -; when -D is used, set DBDELIMITER to the Xargument value. When -F unload is used, change the last SELECT Xstatement into an unload statement.) Contact me for source to SQLCMD. X XThe Makefile has the targets all, dbase and runit. All makes the Xrequisite files, dbase creates and loads the database, and runit runs Xthe various programs. A sample output is included called runit.log. X XNote that both sets of data have random number sequences. The OC code Xwas initially developed with a neat and tidy (non-random) sequence for Xthe identifiers, and a neat and tidy solution was the result. When the Xnumbers were randomised, that solution fell to bits, so the current Xhier2 solution was developed. Beware when adapting the code of this Xpotential problem; simple sample data may easily mislead you. X XJonathan Leffer XInformix Software X@(#)README 1.1 93/03/03 SHAR-EOF chmod 444 README if [ `wc -c Makefile <<'SHAR-EOF' X# @(#)Makefile 1.1 93/03/03 X# X# Makefile for Parts Explosion examples X X.SUFFIXES: .ace .arc X X.ace.arc: X ${ACEPREP} ${AP_FLAGS} $* X X.sh: X rm -f $@ X cp $< $@ X chmod 555 $@ X XFILES.sql = bom.sql hier.sql XFILES.ace = hier2.ace XFILES.sh = partexp1.sh partexp2.sh partexp3.sh partexp4.sh hier1.sh hier2.sh XFILES = ${FILES.sh:.sh=} X Xall: ${FILES.sql} ${FILES} ${FILES.ace} X Xdbase: .dbase X X.dbase: ${FILES.sql} X sqlcmd -e "drop database parts" 2>/dev/null || true X sqlcmd -e "create database parts" X sqlcmd -d parts ${FILES.sql} X touch $@ X Xrunit: dbase all X partexp1 X partexp2 X partexp3 X partexp4 X hier1 X hier2 SHAR-EOF chmod 444 Makefile if [ `wc -c alternatives <<'SHAR-EOF' XDate: Tue, 15 Jun 93 13:40:23 PDT XFrom: cortesi@godzilla (David Cortesi) XTo: tech@godzilla XSubject: Re: Tech - Oracle's "connect by", how do we match it? X X> From proberts@moose Mon Jun 14 21:15:06 1993 X> X> Do we have, or do we plan to have, anything functionally similar to X> Oracle's SQL*Plus's CONNECT BY ? This is an extension to SQL that X> allows you traverse an entire tree in a single query, the parent- X> child relations in the tree being specified in a table. It yields X> a sort of solution to the "parts explosion" problem, frequently X> quoted as something SQL "can't do". X> X> I think we can certainly write a 4GL program to do the same thing, X> and perhaps procedures would also work ... X XA 4GL solution is shown in chapter 28 of 4GL BY EXAMPLE. X XBut the stored procedure idea is a good one. The following stored Xprocedure generates the data for a parts explosion, using the database Xin chapter 28 of 4GL BY EXAMPLE. It has been tested on a 6.0 engine. X XIn order to demo this, you have to create three files. Cut and save Xas indicated below. Then set up to run dbaccess on 5.01 or 6.0, and do: X X dbaccess - < recurse.sql X XDave Cortesi X p.s. perhaps somebody will capture this for tech info? X#! /bin/sh X# This is a shell archive. Remove anything before this line, then feed it X# into a shell via "sh file" or similar. To overwrite existing files, X# type "sh file -c". X# The tool that generated this appeared in the comp.sources.unix newsgroup; X# send mail to comp-sources-unix@uunet.uu.net if you want that tool. X# If this archive is complete, you will see the following message at the end: X# "End of shell archive." X# Contents: recurse.sql ex28pa.unl ex28pt.unl X# Wrapped by johnl@godzilla on Tue Nov 16 10:05:37 1993 XPATH=/bin:/usr/bin:/usr/ucb ; export PATH Xif test -f 'recurse.sql' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'recurse.sql'\" Xelse Xecho shar: Extracting \"'recurse.sql'\" \(1893 characters\) Xsed "s/^X//" >'recurse.sql' <<'END_OF_FILE' XXcreate database recurse; XX XXcreate table parts (partnum integer, descr char(40)); XX XXcreate table partree(parent integer, child integer, used integer); XX XXload from "ex28pa.unl" insert into parts; -- file follows this one XX XXload from "ex28pt.unl" insert into partree; -- file follows that one XX XX-- recursive procedure to do a parts explosion XX XXcreate procedure explode(partno integer, usage integer, level smallint) XX returning -- info on "partno" and its children XX integer -- partno or one of its children XX, char(40) -- its description XX, integer -- its use count XX, smallint -- its level, >= input level XX; XXdefine pn integer; -- current partnum XXdefine pu integer; -- its use count XXdefine cn integer; -- child partnum XXdefine cu integer; -- child use count XXdefine cl smallint; -- child level XXdefine de char(40); -- description XX XX let de = null; XX select descr into de from parts where partnum = partno; XX if de is null then -- no such part, stop now XX return null, null, null, level; XX end if; XX -- return info on this part XX return partno, de, usage, level WITH RESUME; XX -- return info on all children of this part XX foreach XX select child, used XX into pn, pu XX from partree XX where parent = partno XX XX foreach XX execute procedure explode(pn,pu,level+1) XX into cn,de,cu,cl XX XX return cn, de, cu, cl WITH RESUME; XX XX end foreach; -- child at this level XX XX end foreach; -- child of partno XXend procedure; XX XX-- Demonstrate operation of above procedure XX XXexecute procedure explode(76566,1,0); XX XXclose database; XX-- end of "recurse.sql" XEND_OF_FILE Xif test 1893 -ne `wc -c <'recurse.sql'`; then X echo shar: \"'recurse.sql'\" unpacked with wrong size! Xfi X# end of 'recurse.sql' Xfi Xif test -f 'ex28pa.unl' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'ex28pa.unl'\" Xelse Xecho shar: Extracting \"'ex28pa.unl'\" \(2491 characters\) Xsed "s/^X//" >'ex28pa.unl' <<'END_OF_FILE' XX76540|speed nut #8| XX76541|speed nut #2| XX76542|plastic end cap| XX76543|stove bolt| XX76544|flat washer| XX76545|nut| XX76546|bracket mount kit| XX76547|9-in rubber wheel| XX76548|5-in rubber wheel| XX76549|wagon handle assembly| XX76550|wagon handle| XX76551|handle clevis pin| XX76552|swivel fixed plate| XX76553|swivel bolt| XX76554|swivel bolt nut| XX76555|lock washer| XX76556|swivel mount kit| XX76557|axle bracket left| XX76558|axle bracket right| XX76559|wheel pair assembly| XX76560|18-in axle rod| XX76561|18-in fixed axle| XX76562|18-in swivel plate| XX76563|18-in swivelling axle truck| XX76564|large wagon body pan| XX76565|red wagon decal kit| XX76566|large red wagon| XX76567|14-in axle rod| XX76568|14-in fixed axle| XX76569|14-in swivel plate| XX76570|14-in swivelling axle truck| XX76571|small wagon body pan| XX76572|small red wagon| XX76573|12-in axle rod| XX76574|12-in fixed axle| XX76575|12-in swivel plate| XX76576|12-in swivelling axle truck| XX76577|tot wagon decal kit| XX76578|pull-me tot wagon| XX76579|tot wagon body pan| XX76580|2-in axle rod| XX76581|scooter wheel kit| XX76582|toddler scooter decal kit| XX76583|short scooter body| XX76584|short scooter handle| XX76585|plastic handle grip| XX76586|toddler scooter| XX76587|mom's best grocery cart| XX76588|grocery basket assy| XX76589|caddy pan| XX76590|grocery basket| XX76591|caddy top ring| XX76592|dad's fun golf caddy| XX76593|caddy handle| XX76594|basket rim trim| XX76595|caddy base| XX-------------------------------save this as ex28pt.unl ----------------- XX76546|76543|2| XX76546|76544|2| XX76546|76545|2| XX76549|76550|1| XX76549|76551|1| XX76549|76540|1| XX76549|76542|1| XX76556|76552|1| XX76556|76553|1| XX76556|76554|1| XX76556|76555|1| XX76556|76546|2| XX76559|76557|1| XX76559|76558|1| XX76559|76546|2| XX76559|76547|2| XX76559|76541|2| XX76559|76542|2| XX76561|76560|1| XX76561|76559|1| XX76563|76562|1| XX76563|76561|1| XX76563|76556|1| XX76566|76549|1| XX76566|76564|1| XX76566|76565|1| XX76566|76561|1| XX76566|76563|1| XX76568|76567|1| XX76568|76559|1| XX76570|76569|1| XX76570|76568|1| XX76570|76556|1| XX76572|76570|1| XX76572|76549|1| XX76572|76565|1| XX76572|76568|1| XX76572|76571|1| XX76574|76573|1| XX76574|76559|1| XX76576|76575|1| XX76576|76574|1| XX76576|76556|1| XX76578|76577|1| XX76578|76576|1| XX76578|76574|1| XX76578|76579|1| XX76578|76549|1| XX76581|76580|1| XX76581|76540|2| XX76581|76548|2| XX76581|76544|2| XX76581|76542|2| XX76586|76581|2| XX76586|76585|2| XX76586|76582|1| XX76586|76583|1| XX76586|76584|1| XX76587|76588|1| XX76587|76568|1| XX76587|76585|1| XX76587|76595|1| XX76587|76593|1| XX76588|76590|1| XX76588|76594|1| XX76592|76593|1| XX76592|76595|1| XX76592|76591|1| XX76592|76585|1| XX76592|76568|1| XEND_OF_FILE Xif test 2491 -ne `wc -c <'ex28pa.unl'`; then X echo shar: \"'ex28pa.unl'\" unpacked with wrong size! Xfi X# end of 'ex28pa.unl' Xfi Xif test -f 'ex28pt.unl' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'ex28pt.unl'\" Xelse Xecho shar: Extracting \"'ex28pt.unl'\" \(1050 characters\) Xsed "s/^X//" >'ex28pt.unl' <<'END_OF_FILE' XX76546|76543|2| XX76546|76544|2| XX76546|76545|2| XX76549|76550|1| XX76549|76551|1| XX76549|76540|1| XX76549|76542|1| XX76556|76552|1| XX76556|76553|1| XX76556|76554|1| XX76556|76555|1| XX76556|76546|2| XX76559|76557|1| XX76559|76558|1| XX76559|76546|2| XX76559|76547|2| XX76559|76541|2| XX76559|76542|2| XX76561|76560|1| XX76561|76559|1| XX76563|76562|1| XX76563|76561|1| XX76563|76556|1| XX76566|76549|1| XX76566|76564|1| XX76566|76565|1| XX76566|76561|1| XX76566|76563|1| XX76568|76567|1| XX76568|76559|1| XX76570|76569|1| XX76570|76568|1| XX76570|76556|1| XX76572|76570|1| XX76572|76549|1| XX76572|76565|1| XX76572|76568|1| XX76572|76571|1| XX76574|76573|1| XX76574|76559|1| XX76576|76575|1| XX76576|76574|1| XX76576|76556|1| XX76578|76577|1| XX76578|76576|1| XX76578|76574|1| XX76578|76579|1| XX76578|76549|1| XX76581|76580|1| XX76581|76540|2| XX76581|76548|2| XX76581|76544|2| XX76581|76542|2| XX76586|76581|2| XX76586|76585|2| XX76586|76582|1| XX76586|76583|1| XX76586|76584|1| XX76587|76588|1| XX76587|76568|1| XX76587|76585|1| XX76587|76595|1| XX76587|76593|1| XX76588|76590|1| XX76588|76594|1| XX76592|76593|1| XX76592|76595|1| XX76592|76591|1| XX76592|76585|1| XX76592|76568|1| XEND_OF_FILE Xif test 1050 -ne `wc -c <'ex28pt.unl'`; then X echo shar: \"'ex28pt.unl'\" unpacked with wrong size! Xfi X# end of 'ex28pt.unl' Xfi Xecho shar: End of shell archive. Xexit 0 X XFrom: hermannd@infmuc (Hermann Daeubler) XSubject: ESQL/C and stored procedure example XTo: tech@infmuc XDate: Wed, 20 May 92 11:07:10 MET DST X XHello, X Xa little example which shows the use of a stored procedure in ESQL/C. X XYou need : ESQL/C 5.0 X OnLine 5.0 X Bourne shell X XTake the following sh script and start it. It generates all .ec files Xand stored procedure sources ... and starts the program. X XImportant : X=========== XTo run the script several times I included a drop database statement. XSo be sure that you have no database with the name "spl_test" !!!!!!! X( if there is no database spl_test you get an error -329 from the X drop database statement -> ignore it ) X XWhat shows the example ? X Xit shows a way to solve the part explosion problem. You have one table Xand in this table you have parts and sub parts ( part A consists of Xpart A1 and part A2 .... ). XNow you want a list of all parts and their sub parts : X Xpart A X part A1 X part A2 Xpart B X part B1 X part B1a X part B1b X part B2 X part B3 X. X XTo make a simple solution I included a field in the table which shows if Xthe part is a sub part ( "T" = top, "S" = sub ). In the list above Xpart A and part B have type "T" the rest has type "S". I used it because Xif there is no flag it is too difficult to find the parts which are not Xsub parts with a select. XTo solve the problem that you don't know how many sub parts are available Xfor a "top" part I used a recursively calling procedure. X XIt is a quick and dirty example, so there could be better solutions ! X XHermann the German X( Support - Informix Germany ) X X#! /bin/sh X# This is a shell archive. Remove anything before this line, then feed it X# into a shell via "sh file" or similar. To overwrite existing files, X# type "sh file -c". X# The tool that generated this appeared in the comp.sources.unix newsgroup; X# send mail to comp-sources-unix@uunet.uu.net if you want that tool. X# If this archive is complete, you will see the following message at the end: X# "End of shell archive." X# Contents: createdb.ec createpr.ec partslist.ec runit.sh X# sub_parts.prc X# Wrapped by johnl@godzilla on Tue Nov 16 10:23:05 1993 XPATH=/bin:/usr/bin:/usr/ucb ; export PATH Xif test -f 'createdb.ec' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'createdb.ec'\" Xelse Xecho shar: Extracting \"'createdb.ec'\" \(1685 characters\) Xsed "s/^X//" >'createdb.ec' <<'END_OF_FILE' XX#include XX$include sqlca; XX$include sqlda; XX XXmain() XX{ XX XX $drop database spl_test; XX sql_error(); XX XX $create database spl_test; XX sql_error(); XX XX $create table parts ( part_nr integer, XX part_name char(30), XX sub_part_nr integer, XX top_or_not char(1) ); XX sql_error(); XX XX $insert into parts values( 1,"Part1", 10,"T"); XX sql_error(); XX $insert into parts values(100,"Part2", 110,"T"); XX sql_error(); XX $insert into parts values(200,"Part3", NULL,"T"); XX sql_error(); XX $insert into parts values(300,"Part4", 310,"T"); XX sql_error(); XX $insert into parts values( 10,"Part1-1", NULL,"S"); XX sql_error(); XX $insert into parts values( 10,"Part1-2", NULL,"S"); XX sql_error(); XX $insert into parts values( 10,"Part1-3", 20,"S"); XX sql_error(); XX $insert into parts values(110,"Part2-1", NULL,"S"); XX sql_error(); XX $insert into parts values(110,"Part2-2", NULL,"S"); XX sql_error(); XX $insert into parts values(310,"Part4-1", 320,"S"); XX sql_error(); XX $insert into parts values(310,"Part4-2", NULL,"S"); XX sql_error(); XX $insert into parts values( 20,"Part1-3-a", NULL,"S"); XX sql_error(); XX $insert into parts values( 20,"Part1-3-b", NULL,"S"); XX sql_error(); XX $insert into parts values(320,"Part4-1-a", 330,"S"); XX sql_error(); XX $insert into parts values(330,"Part4-1-a.1",NULL,"S"); XX sql_error(); XX XX $close database; XX sql_error(); XX} XX XXsql_error() XX{ XX char err_str[300]; XX XX if( sqlca.sqlcode != 0 ) XX { XX rgetmsg(sqlca.sqlcode,err_str,255); XX fprintf(stderr,"error %d : %s\n",sqlca.sqlcode,err_str); XX } XX} XEND_OF_FILE Xif test 1685 -ne `wc -c <'createdb.ec'`; then X echo shar: \"'createdb.ec'\" unpacked with wrong size! Xfi X# end of 'createdb.ec' Xfi Xif test -f 'createpr.ec' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'createpr.ec'\" Xelse Xecho shar: Extracting \"'createpr.ec'\" \(373 characters\) Xsed "s/^X//" >'createpr.ec' <<'END_OF_FILE' XX#include XX$include sqlda; XX$include sqlca; XX XXmain() XX{ XX $database spl_test; XX sql_error(); XX XX $create procedure from "sub_parts.prc"; XX sql_error(); XX XX $close database; XX sql_error(); XX} XX XXsql_error() XX{ XX char err_str[300]; XX XX if( sqlca.sqlcode != 0 ) XX { XX rgetmsg(sqlca.sqlcode,err_str,255); XX fprintf(stderr,"error %d : %s\n",sqlca.sqlcode,err_str); XX } XX} XEND_OF_FILE Xif test 373 -ne `wc -c <'createpr.ec'`; then X echo shar: \"'createpr.ec'\" unpacked with wrong size! Xfi X# end of 'createpr.ec' Xfi Xif test -f 'partslist.ec' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'partslist.ec'\" Xelse Xecho shar: Extracting \"'partslist.ec'\" \(1596 characters\) Xsed "s/^X//" >'partslist.ec' <<'END_OF_FILE' XX#include XX$include sqlda; XX$include sqlca; XX XX#define LENMARGIN 81 XX$define LEFTMARGIN 4; XX XX$long part_nr; XX$char part_name[31]; XX$long sub_part_nr; XX$char top_or_not; XX XX$int nr_of_blanks; XX$char sub_part_name[31]; XX XXchar left_margin[LENMARGIN]; XX XXmain() XX{ XX int i; XX XX for(i=0;i'runit.sh' <<'END_OF_FILE' XXecho "compiling createdb.ec" XXesql createdb.ec -o createdb XXecho "creating the testdatabase" XX./createdb XX XXecho "compiling createpr.ec" XXesql createpr.ec -o createpr XXecho "creating the stored procedure" XX./createpr XX XXecho "compiling partslist.ec" XXesql partslist.ec -o partslist XXecho XXecho "executing partslist" XXecho XX./partslist XEND_OF_FILE Xif test 320 -ne `wc -c <'runit.sh'`; then X echo shar: \"'runit.sh'\" unpacked with wrong size! Xfi X# end of 'runit.sh' Xfi Xif test -f 'sub_parts.prc' -a "${1}" != "-c" ; then X echo shar: Will not clobber existing file \"'sub_parts.prc'\" Xelse Xecho shar: Extracting \"'sub_parts.prc'\" \(599 characters\) Xsed "s/^X//" >'sub_parts.prc' <<'END_OF_FILE' XXcreate procedure sub_parts(p_nr int,nr_of_blanks int) XX returning char(30),int; XX XX define p_name char(30); XX define sub_p_nr, h_nr_of_blanks int; XX XX foreach XX select part_name,sub_part_nr into p_name,sub_p_nr XX from parts where part_nr = p_nr XX XX if sub_p_nr is not null then XX return p_name,nr_of_blanks with resume; XX XX foreach execute procedure XX sub_parts(sub_p_nr,nr_of_blanks+4) into p_name, h_nr_of_blanks XX XX return p_name, h_nr_of_blanks with resume; XX end foreach XX else XX return p_name,nr_of_blanks with resume; XX end if XX end foreach; XX XXend procedure XEND_OF_FILE Xif test 599 -ne `wc -c <'sub_parts.prc'`; then X echo shar: \"'sub_parts.prc'\" unpacked with wrong size! Xfi X# end of 'sub_parts.prc' Xfi Xecho shar: End of shell archive. Xexit 0 X XFrom: johnl (Jonathan Leffler) XDate: Thu Mar 4 12:01:43 1993 XTo: cwilson@atlanta XSubject: Re: Hierarchical Structures in SQL Databases X XHere's my stuff. X XAbout depth of search/hierarchy. X XIt depends on whether you are doing a Parts Explosion (Bill of Materials or XBoM) or Organisation Chart type search. X XIn the BoM solution, there is a simple iterative solution which does, Xroughly: X X while (select count(*) from ...) > 0 X do another scan X end while X Xwhere the select condition counts the incompletely expanded parts. This is Xquite simple because the table being used has the same structure on every Xiteration. This means that you do not have to know exactly how deep your Xsearch goes before you do it. X XIn the Organisation Chart solution, the table structure changes Xiteratively, which is a confounded nuisance (believe me!). Although an Xiterative solution could be provided, it would have to include a Xdynamically constructed create temp table statement as well as dynamically Xconstructed select statements, so it is considerably more complex. To Xavoid the iterative solution, you could write out the expansion long-hand, Xbut then you would have to ensure that the number of written out iterations Xwas sufficient for the deepest search the code would be asked to do. I Xsuspect that few hierarchies (even in government circles) would extend to X10 deep; 20 would be utterly exceptional. However, if the hierarchy was Xonly 4 deep, the cost of doing the extra 16 iterations would be Xnon-negligible. If you look at the hier* code in the shell archive, Xyou'll see what I mean, I think. If not, come back to me on it. X XYours, XJonathan Leffler (johnl@obelix) X X SHAR-EOF chmod 664 alternatives if [ `wc -c bom.sql <<'SHAR-EOF' X-- @(#)bom.sql 1.3 93/02/27 X-- Parts explosion (schema and data) X XCREATE TABLE parts X( X pno CHAR(3) NOT NULL X PRIMARY KEY CONSTRAINT pk_part, X pname VARCHAR(30) NOT NULL X); X XINSERT INTO parts VALUES("P02", "Pump Vanes"); XINSERT INTO parts VALUES("P10", "Pump Assembly"); XINSERT INTO parts VALUES("P11", "Pump Rotor"); XINSERT INTO parts VALUES("P12", "Pump Housing A"); XINSERT INTO parts VALUES("P13", "Pump Seals"); XINSERT INTO parts VALUES("P14", "Pump Housing B"); XINSERT INTO parts VALUES("P16", "Annealed flange 5D"); XINSERT INTO parts VALUES("P17", "Base Assembly P"); XINSERT INTO parts VALUES("P18", "Baseplate R3"); XINSERT INTO parts VALUES("P19", "3mm D x 70mm Rotor Shaft"); XINSERT INTO parts VALUES("P21", "Mounting Plate R3"); XINSERT INTO parts VALUES("P23", "Vane Springs"); XINSERT INTO parts VALUES("P24", "4mm x M1.5 Bolt"); XINSERT INTO parts VALUES("P26", "3mm x M1.2 Bolt"); XINSERT INTO parts VALUES("P27", "Base Bearing"); XINSERT INTO parts VALUES("P28", "Bearing Outer Case"); XINSERT INTO parts VALUES("P29", "Bearing Inner Case"); XINSERT INTO parts VALUES("P30", "0.35mm Ball Bearings"); XINSERT INTO parts VALUES("P32", "6mm D x 37mm Connecting hose"); XINSERT INTO parts VALUES("P33", "6mm Jubilee clip"); XINSERT INTO parts VALUES("P34", "3mm x M1.5 Bolt"); XINSERT INTO parts VALUES("P35", "M1.5 Nut"); XINSERT INTO parts VALUES("P36", "6mm Jubilee clip body"); XINSERT INTO parts VALUES("P07", "6mm D x 37mm Hose Assembly"); XINSERT INTO parts VALUES("P68", "12mm D x 30T Cog (3mm bore)"); XINSERT INTO parts VALUES("P39", "47mm D x 150T Cog (6mm bore)"); XINSERT INTO parts VALUES("P40", "2mm x M1.2 Grub Screw"); XINSERT INTO parts VALUES("P41", "Bearing Cage"); XINSERT INTO parts VALUES("P45", "6mm shakeproof washer"); XINSERT INTO parts VALUES("P47", "Annealed flange 6B"); XINSERT INTO parts VALUES("P49", "Pump Sub-assembly"); XINSERT INTO parts VALUES("P55", "13mm x M2.5 Bolt"); XINSERT INTO parts VALUES("P56", "Baseplate type A"); XINSERT INTO parts VALUES("P62", "Widget Sub-assembly A"); XINSERT INTO parts VALUES("P71", "Widget Assembly"); XINSERT INTO parts VALUES("P72", "Pump Vane Housing"); XINSERT INTO parts VALUES("P74", "25mm x M6 Bolt"); XINSERT INTO parts VALUES("P85", "1.5mm Shakeproof Washer"); XINSERT INTO parts VALUES("P88", "Baseplate type B"); XINSERT INTO parts VALUES("P93", "Widget Sub-assembly B"); X XCREATE TABLE bom X( X p1 CHAR(3) NOT NULL X REFERENCES parts CONSTRAINT f1_bom, X p2 CHAR(3) NOT NULL X REFERENCES parts CONSTRAINT f2_bom, X qty INTEGER NOT NULL, X PRIMARY KEY (p1,p2) CONSTRAINT pk_bom X); X XINSERT INTO bom VALUES("P71", "P62", 2); -- Widget Assembly XINSERT INTO bom VALUES("P71", "P93", 1); XINSERT INTO bom VALUES("P71", "P74", 4); XINSERT INTO bom VALUES("P71", "P45", 3); XINSERT INTO bom VALUES("P71", "P49", 1); XINSERT INTO bom VALUES("P62", "P74", 2); -- Widget Sub-assembly A XINSERT INTO bom VALUES("P62", "P56", 1); XINSERT INTO bom VALUES("P62", "P47", 2); XINSERT INTO bom VALUES("P93", "P74", 6); -- Widget Sub-assembly B XINSERT INTO bom VALUES("P93", "P45", 6); XINSERT INTO bom VALUES("P93", "P47", 3); XINSERT INTO bom VALUES("P93", "P88", 1); XINSERT INTO bom VALUES("P49", "P74", 10); -- Pump Sub-assembly XINSERT INTO bom VALUES("P49", "P45", 10); XINSERT INTO bom VALUES("P49", "P10", 2); XINSERT INTO bom VALUES("P49", "P07", 2); XINSERT INTO bom VALUES("P49", "P39", 1); XINSERT INTO bom VALUES("P49", "P40", 3); XINSERT INTO bom VALUES("P10", "P11", 1); -- Pump Assembly XINSERT INTO bom VALUES("P10", "P12", 1); XINSERT INTO bom VALUES("P10", "P13", 2); XINSERT INTO bom VALUES("P10", "P14", 1); XINSERT INTO bom VALUES("P10", "P55", 16); XINSERT INTO bom VALUES("P10", "P16", 2); XINSERT INTO bom VALUES("P10", "P17", 1); XINSERT INTO bom VALUES("P10", "P18", 1); XINSERT INTO bom VALUES("P10", "P07", 2); XINSERT INTO bom VALUES("P10", "P68", 1); XINSERT INTO bom VALUES("P10", "P40", 1); XINSERT INTO bom VALUES("P11", "P19", 1); -- Rotor XINSERT INTO bom VALUES("P11", "P02", 4); XINSERT INTO bom VALUES("P11", "P72", 1); XINSERT INTO bom VALUES("P11", "P23", 8); XINSERT INTO bom VALUES("P11", "P24", 8); XINSERT INTO bom VALUES("P11", "P85", 8); XINSERT INTO bom VALUES("P11", "P26", 4); XINSERT INTO bom VALUES("P18", "P21", 2); -- Baseplate R3 XINSERT INTO bom VALUES("P18", "P24", 4); XINSERT INTO bom VALUES("P18", "P26", 4); XINSERT INTO bom VALUES("P18", "P27", 1); XINSERT INTO bom VALUES("P27", "P28", 1); -- Base Bearing R3 XINSERT INTO bom VALUES("P27", "P29", 1); XINSERT INTO bom VALUES("P27", "P30", 12); XINSERT INTO bom VALUES("P27", "P41", 1); XINSERT INTO bom VALUES("P07", "P32", 1); -- Hose Assembly XINSERT INTO bom VALUES("P07", "P33", 2); XINSERT INTO bom VALUES("P33", "P34", 1); -- Jubilee Clip XINSERT INTO bom VALUES("P33", "P35", 1); XINSERT INTO bom VALUES("P33", "P36", 1); SHAR-EOF chmod 444 bom.sql if [ `wc -c hier.sql <<'SHAR-EOF' X-- @(#)hier.sql 1.2 93/02/27 X-- @(#)Table and sample data for illustrating hierarchical data X XCREATE TABLE hierarchy X( X body INTEGER NOT NULL, X name CHAR(25) NOT NULL, X boss INTEGER NOT NULL X); X XINSERT INTO hierarchy VALUES (12, "Sales Manager 3", 25); XINSERT INTO hierarchy VALUES (13, "Sales Person 1", 12); XINSERT INTO hierarchy VALUES (14, "Sales Person 2", 50); XINSERT INTO hierarchy VALUES (16, "Sales Person 4", 61); XINSERT INTO hierarchy VALUES (18, "Sales Person 6", 50); XINSERT INTO hierarchy VALUES (19, "Sales Person 7", 12); XINSERT INTO hierarchy VALUES (21, "Junior Accountant 2", 39); XINSERT INTO hierarchy VALUES (24, "Junior Accountant 5", 48); XINSERT INTO hierarchy VALUES (25, "Sales Director", 71); XINSERT INTO hierarchy VALUES (26, "Junior Accountant 7", 48); XINSERT INTO hierarchy VALUES (27, "Junior Accountant 8", 39); XINSERT INTO hierarchy VALUES (28, "Junior Accountant 9", 39); XINSERT INTO hierarchy VALUES (29, "Accountancy Clerk 1", 72); XINSERT INTO hierarchy VALUES (30, "Accountancy Clerk 2", 24); XINSERT INTO hierarchy VALUES (31, "Technical Director", 71); XINSERT INTO hierarchy VALUES (32, "Accountancy Clerk 4", 90); XINSERT INTO hierarchy VALUES (37, "Accountant 1", 66); XINSERT INTO hierarchy VALUES (38, "Development Manager", 31); XINSERT INTO hierarchy VALUES (39, "Accountant 3", 66); XINSERT INTO hierarchy VALUES (44, "Marketing Director", 71); XINSERT INTO hierarchy VALUES (45, "Junior Accountant 6", 37); XINSERT INTO hierarchy VALUES (48, "Accountant 2", 66); XINSERT INTO hierarchy VALUES (50, "Sales Manager 1", 25); XINSERT INTO hierarchy VALUES (53, "Junior Accountant 4", 48); XINSERT INTO hierarchy VALUES (57, "Sales Person 5", 12); XINSERT INTO hierarchy VALUES (61, "Sales Manager 2", 25); XINSERT INTO hierarchy VALUES (62, "Development Engineer 2", 38); XINSERT INTO hierarchy VALUES (63, "Accountancy Clerk 5", 90); XINSERT INTO hierarchy VALUES (64, "Development Engineer 1", 38); XINSERT INTO hierarchy VALUES (66, "Chief Accountant", 92); XINSERT INTO hierarchy VALUES (71, "President", 0); XINSERT INTO hierarchy VALUES (72, "Junior Accountant 3", 37); XINSERT INTO hierarchy VALUES (82, "Accountancy Clerk 3", 45); XINSERT INTO hierarchy VALUES (85, "Sales Person 3", 61); XINSERT INTO hierarchy VALUES (90, "Junior Accountant 1", 37); XINSERT INTO hierarchy VALUES (92, "Finance Director", 71); XINSERT INTO hierarchy VALUES (94, "Marketing Executive", 44); X XCREATE UNIQUE INDEX pk_hierarchy ON hierarchy(body); SHAR-EOF chmod 444 hier.sql if [ `wc -c hier1.ace <<'SHAR-EOF' X-- @(#)hier1.ace 1.1 93/02/27 X-- @(#)Illustrating the Depth-First-Search (DFS) Technique for Hierarchies X X-- This illustrates how a Breadth-First-Search (BFS) Technique can be X-- persuaded to return data so that it can be processed in DFS order. X-- There is an inductive procedure for generating the Nth temporary X-- table for N > 1. The N = 0 table is produced completely differently, X-- and the N = 1 method is slightly different from the N > 1 method. X-- Note that an extra column is added to the temporary table every time. X-- Note the trick of inserting level before b0 in the ORDER BY clause. X-- X-- More work is needed to investigate whether a surreptitious use of X-- SERIAL columns will allow the number of extra columns to be limited. X-- X-- Note that you have to pre-determine how many iterations of SELECTION X-- are necessary. At the start of each iteration, you could use the X-- statement: SELECT COUNT(*) FROM tN, hierarchy h WHERE tN.b0 = h.boss; X-- If the answer is zero, no more iterations are required. X XDATABASE parts END X XDEFINE X variable n INTEGER XEND X XOUTPUT X TOP MARGIN 0 X BOTTOM MARGIN 0 X LEFT MARGIN 0 XEND X X-- N = 0 XSELECT body b0, 0 level X FROM hierarchy X WHERE boss = 0 -- Choose your own criteria here! X INTO TEMP t0; X X--SELECT "T0" tag, t0.*, h.name X-- FROM t0, hierarchy h X-- WHERE t0.b0 = h.body X-- ORDER BY level, b0; X X-- N = 1 XSELECT t0.b0 b1, t0.b0 b0, t0.level level X FROM t0 XUNION XSELECT t0.b0, h.body, (t0.level + 1) X FROM hierarchy h, t0 X WHERE t0.b0 = h.boss X INTO TEMP t1; X X--SELECT "T1" tag, t1.*, h.name X-- FROM t1, hierarchy h X-- WHERE t1.b0 = h.body X-- ORDER BY b1, level, b0; X X-- N = 2: Start inductive procedure! XSELECT t1.b1 b2, t1.b0 b1, t1.b0, t1.level X FROM t1 XUNION XSELECT t1.b1, t1.b0, h.body, (t1.level + 1) X FROM hierarchy h, t1 X WHERE t1.b0 = h.boss X AND t1.b1 != t1.b0 X INTO TEMP t2; X X--SELECT "T2" tag, t2.*, h.name X-- FROM t2, hierarchy h X-- WHERE t2.b0 = h.body X-- ORDER BY b2, b1, level, b0; X X-- N = 3 XSELECT b2 b3, b1 b2, b0 b1, b0, level X FROM t2 XUNION XSELECT b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t2 X WHERE t2.b0 = h.boss X AND t2.b1 != t2.b0 X INTO TEMP t3; X X--SELECT "T3" tag, t3.*, h.name X-- FROM t3, hierarchy h X-- WHERE t3.b0 = h.body X-- ORDER BY b3, b2, b1, level, b0; X X-- N = 4 XSELECT b3 b4, b2 b3, b1 b2, b0 b1, b0, level X FROM t3 XUNION XSELECT b3, b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t3 X WHERE t3.b0 = h.boss X AND t3.b1 != t3.b0 X INTO TEMP t4; X X--SELECT "T4" tag, t4.*, h.name X-- FROM t4, hierarchy h X-- WHERE t4.b0 = h.body X-- ORDER BY b4, b3, b2, b1, level, b0; X X-- N = 5 XSELECT b4 b5, b3 b4, b2 b3, b1 b2, b0 b1, b0, level X FROM t4 XUNION XSELECT b4, b3, b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t4 X WHERE t4.b0 = h.boss X AND t4.b1 != t4.b0 X INTO TEMP t5; X XSELECT "T5" tag, t5.*, h.name X FROM t5, hierarchy h X WHERE t5.b0 = h.body X ORDER BY b5, b4, b3, b2, b1, level, b0 X XEND X XFORMAT X XON EVERY ROW X LET n = 3 * level + 1 X PRINT COLUMN n, name CLIPPED X XEND SHAR-EOF chmod 444 hier1.ace if [ `wc -c hier1.sh <<'SHAR-EOF' X: "@(#)hier1.sh 1.1 93/02/27" X# X# @(#)Query sequence to demonstrate Depth-First-Search Techniques. X X# Sorting the results is a major problem! X# Given: X# RANGE h IS hierarchy X# FORALL h (h.boss < h.body) X# then: the sort can be done on the basis of body number. X# X# However, if the official body numbers are allocated arbitrarily, then X# the technique used near the bottom of this file allocates new sequence X# numbers which satisfy the criterion above. It works, but it is very X# messy, and it could not be used in an ACE report. X Xcat <<'EOF' | X XSELECT body b0, 0 level X FROM hierarchy X WHERE boss = 0 X INTO TEMP t0; X XSELECT "T0" tag, t0.*, h.name X FROM t0, hierarchy h X WHERE t0.b0 = h.body X ORDER BY level, b0; X X-- Start inductive procedure! XSELECT t0.b0 b1, t0.b0 b0, t0.level level X FROM t0 XUNION XSELECT t0.b0, h.body, (t0.level + 1) X FROM hierarchy h, t0 X WHERE t0.b0 = h.boss X INTO TEMP t1; X XSELECT "T1" tag, t1.*, h.name X FROM t1, hierarchy h X WHERE t1.b0 = h.body X ORDER BY b1, level, b0; X XSELECT t1.b1 b2, t1.b0 b1, t1.b0, t1.level X FROM t1 XUNION XSELECT t1.b1, t1.b0, h.body, (t1.level + 1) X FROM hierarchy h, t1 X WHERE t1.b0 = h.boss X AND t1.b1 != t1.b0 X INTO TEMP t2; X XSELECT "T2" tag, t2.*, h.name X FROM t2, hierarchy h X WHERE t2.b0 = h.body X ORDER BY b2, b1, level, b0; X XSELECT b2 b3, b1 b2, b0 b1, b0, level X FROM t2 XUNION XSELECT b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t2 X WHERE t2.b0 = h.boss X AND t2.b1 != t2.b0 X INTO TEMP t3; X XSELECT "T3" tag, t3.*, h.name X FROM t3, hierarchy h X WHERE t3.b0 = h.body X ORDER BY b3, b2, b1, level, b0; X XSELECT b3 b4, b2 b3, b1 b2, b0 b1, b0, level X FROM t3 XUNION XSELECT b3, b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t3 X WHERE t3.b0 = h.boss X AND t3.b1 != t3.b0 X INTO TEMP t4; X XSELECT "T4" tag, t4.*, h.name X FROM t4, hierarchy h X WHERE t4.b0 = h.body X ORDER BY b4, b3, b2, b1, level, b0; X XSELECT b4 b5, b3 b4, b2 b3, b1 b2, b0 b1, b0, level X FROM t4 XUNION XSELECT b4, b3, b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t4 X WHERE t4.b0 = h.boss X AND t4.b1 != t4.b0 X INTO TEMP t5; X XSELECT "T5" tag, t5.*, h.name X FROM t5, hierarchy h X WHERE t5.b0 = h.body X ORDER BY b5, b4, b3, b2, b1, level, b0; X X-- ******************************** X-- ** The fun starts here! ** X-- ** This cannot be done in ACE ** X-- ******************************** XCREATE TEMP TABLE t6 X( X seq SERIAL, X b5 INTEGER, X b4 INTEGER, X b3 INTEGER, X b2 INTEGER, X b1 INTEGER, X b0 INTEGER, X level INTEGER X); X X-- Ensure that all the level 0 people have smaller numbers sequence numbers X-- than the level 1 people, etc. XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 0; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 1; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 2; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 3; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 4; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 5; X XSELECT "T6" tag, t6.*, h.name X FROM t6, hierarchy h X WHERE t6.b0 = h.body X ORDER BY level, seq, b5, b4, b3, b2, b1, b0; X X-- Now, remap the body number columns to use the newly allocated X-- sequence numbers. Note that the self-join of t6r and t6a could be X-- eliminated and t6a would supply the values labelled body and level X-- instead. However, the supplied solution is more symmetric, and X-- performance isn't an issue when proving the technology! X XSELECT t6f.seq b5, X t6e.seq b4, X t6d.seq b3, X t6c.seq b2, X t6b.seq b1, X t6a.seq b0, X t6r.b0 body, X t6r.level X FROM X t6 t6r, -- Reference X t6 t6a, -- Self-join for b0 X t6 t6b, -- Self-join for b1 X t6 t6c, -- Self-join for b2 X t6 t6d, -- Self-join for b3 X t6 t6e, -- Self-join for b4 X t6 t6f -- Self-join for b5 X WHERE t6r.b5 = t6f.b0 X AND t6r.b4 = t6e.b0 X AND t6r.b3 = t6d.b0 X AND t6r.b2 = t6c.b0 X AND t6r.b1 = t6b.b0 X AND t6r.b0 = t6a.b0 XINTO TEMP T7; X X-- Now we can sequence the data correctly (adequately) by sorting on X-- all the re-allocated body number columns. X XSELECT "T7" tag, t7.*, h.name X FROM t7, hierarchy h X WHERE t7.body = h.body X ORDER BY b5, b4, b3, b2, b1, b0; XEOF X Xsqlcmd -D' ' -d parts -f - | Xawk -F' ' '# X{ X # Print table name and body numbers in 4-character fields X printf("%-2s ", $1); X for (i = 2; i < NF; i++) X printf("%2d ", $i); X printf("%s\n",$NF); X}' SHAR-EOF chmod 444 hier1.sh if [ `wc -c hier2.ace <<'SHAR-EOF' X-- @(#)hier2.ace 1.2 93/03/03 X-- @(#)Print Formatting for Hierarchical (Breadth-First-Search) Selection X XDATABASE ASCII END X XDEFINE X VARIABLE n INTEGER X ASCII X b6 INTEGER, X b5 INTEGER, X b4 INTEGER, X b3 INTEGER, X b2 INTEGER, X b1 INTEGER, X b0 INTEGER, X level INTEGER, X name CHAR(30) XEND X XOUTPUT X TOP MARGIN 0 X BOTTOM MARGIN 0 X LEFT MARGIN 0 XEND X XREAD "hier2.out" DELIMITER "|" X XEND X XFORMAT X XON EVERY ROW X LET n = 3 * level + 1 X PRINT COLUMN n, name CLIPPED X XEND SHAR-EOF chmod 444 hier2.ace if [ `wc -c hier2.sh <<'SHAR-EOF' X: "@(#)hier2.sh 1.1 93/03/03" X# X# @(#)Query sequence to demonstrate Depth-First-Search Techniques. X X# Sorting the results is a major problem! X# Given: X# RANGE h IS hierarchy X# FORALL h (h.boss < h.body) X# then: the sort can be done on the basis of body number. X# X# However, if the official body numbers are allocated arbitrarily, then X# the technique used near the bottom of this file allocates new sequence X# numbers which satisfy the criterion above. It works, but it is very X# messy, and it could not be used in an ACE report. X Xcat <<'EOF' | X XSELECT body b0, 0 level X FROM hierarchy X WHERE boss = 0 XINTO TEMP t0; X X-- Start inductive procedure! XSELECT t0.b0 b1, t0.b0 b0, t0.level level X FROM t0 XUNION XSELECT t0.b0, h.body, (t0.level + 1) X FROM hierarchy h, t0 X WHERE t0.b0 = h.boss XINTO TEMP t1; X XSELECT t1.b1 b2, t1.b0 b1, t1.b0, t1.level X FROM t1 XUNION XSELECT t1.b1, t1.b0, h.body, (t1.level + 1) X FROM hierarchy h, t1 X WHERE t1.b0 = h.boss X AND t1.b1 != t1.b0 XINTO TEMP t2; X XSELECT b2 b3, b1 b2, b0 b1, b0, level X FROM t2 XUNION XSELECT b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t2 X WHERE t2.b0 = h.boss X AND t2.b1 != t2.b0 XINTO TEMP t3; X XSELECT b3 b4, b2 b3, b1 b2, b0 b1, b0, level X FROM t3 XUNION XSELECT b3, b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t3 X WHERE t3.b0 = h.boss X AND t3.b1 != t3.b0 XINTO TEMP t4; X XSELECT b4 b5, b3 b4, b2 b3, b1 b2, b0 b1, b0, level X FROM t4 XUNION XSELECT b4, b3, b2, b1, b0, body, (level + 1) level X FROM hierarchy h, t4 X WHERE t4.b0 = h.boss X AND t4.b1 != t4.b0 XINTO TEMP t5; X X-- ******************************** X-- ** The fun starts here! ** X-- ** This cannot be done in ACE ** X-- ******************************** XCREATE TEMP TABLE t6 X( X seq SERIAL, X b5 INTEGER, X b4 INTEGER, X b3 INTEGER, X b2 INTEGER, X b1 INTEGER, X b0 INTEGER, X level INTEGER X); X X-- Ensure that all the level 0 people have smaller numbers sequence numbers X-- than the level 1 people, etc. XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 0; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 1; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 2; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 3; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 4; XINSERT INTO t6 X SELECT 0, b5, b4, b3, b2, b1, b0, level X FROM t5 X WHERE level = 5; X X-- Now, remap the body number columns to use the newly allocated X-- sequence numbers. Note that the self-join of t6r and t6a could be X-- eliminated and t6a would supply the values labelled body and level X-- instead. However, the supplied solution is more symmetric, and X-- performance isn't an issue when proving the technology! X XSELECT t6f.seq b5, X t6e.seq b4, X t6d.seq b3, X t6c.seq b2, X t6b.seq b1, X t6a.seq b0, X t6r.b0 body, X t6r.level X FROM X t6 t6r, -- Reference X t6 t6a, -- Self-join for b0 X t6 t6b, -- Self-join for b1 X t6 t6c, -- Self-join for b2 X t6 t6d, -- Self-join for b3 X t6 t6e, -- Self-join for b4 X t6 t6f -- Self-join for b5 X WHERE t6r.b5 = t6f.b0 X AND t6r.b4 = t6e.b0 X AND t6r.b3 = t6d.b0 X AND t6r.b2 = t6c.b0 X AND t6r.b1 = t6b.b0 X AND t6r.b0 = t6a.b0 XINTO TEMP T7; X X-- Now we can sequence the data correctly (adequately) by sorting on X-- all the re-allocated body number columns. X XSELECT t7.*, h.name X FROM t7, hierarchy h X WHERE t7.body = h.body X ORDER BY b5, b4, b3, b2, b1, b0; XEOF X Xsqlcmd -F unload -D'|' -d parts -f - >hier2.out X Xsaceprep -q hier2 && sacego -q hier2 SHAR-EOF chmod 444 hier2.sh if [ `wc -c partexp1.sh <<'SHAR-EOF' X#!/bin/ksh X# X# "@(#)partexp1.sh 1.1 93/02/27" X# X# Run parts explosion. X X# This parts explosion produces the picking list of elementary X# components needed to build 1 unit of any particular assembly. X Xtmp=${TMPDIR:-/tmp}/mk2.$$ Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15 Xp1=${tmp}.1 Xp2=${tmp}.2 Xp3=${tmp}.3 Xp4=${tmp}.4 Xp5=${tmp}.5 X Xcat >$p1 <<'EOF' XSELECT * X FROM bom X INTO TEMP t1; X XSELECT "Number of parts not expanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1; XEOF X Xcat >$p2 <<'EOF' XSELECT p1, p2, qty X FROM t1 X WHERE p2 NOT IN (SELECT p1 FROM bom) XUNION ALL XSELECT t1.p1, bom.p2, SUM(bom.qty * t1.qty) qty X FROM bom, t1 X WHERE t1.p2 = bom.p1 X GROUP BY 1, 2 XINTO TEMP t0; X XDELETE FROM t1; X XINSERT INTO t1 X SELECT p1, p2, SUM(qty) qty X FROM t0 X GROUP BY p1, p2; X XDROP TABLE t0; X XSELECT "Number of parts not expanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1; XEOF X Xcat >$p3 <<'EOF' XSELECT t1.qty, p1.pname p1, p2.pname p2 X FROM parts p1, parts p2, t1 X WHERE t1.p1 = p1.pno X AND t1.p2 = p2.pno X ORDER BY p1, qty; XEOF X Xcat >$p4 <<'EOF' XSELECT "--", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1; XEOF X Xiterations="-e 'SET EXPLAIN ON'" Xiterations="" X((i=1)) Xwhile ((i<10)) Xdo X echo "`date`: Level $i" X { X echo "\nLevel $i" X eval sqlcmd "-D' '" -d parts $p1 $iterations $p4 $p3 X } >$p5 X grep "^--[ ]*0$" $p5 >/dev/null && break X iterations="$iterations $p2" X ((i=i+1)) Xdone Xecho "`date`: Done" X Xcat $p5 | Xawk -F' ' '# X/^--/ { next; } X { X if (NF != 3) X print; X else X { X if ($2 != old) X { X print ""; X old = $2; X } X printf("%4d %-32s %-32s\n", $1, $2, $3); X } X }' X Xrm -f ${tmp}.* SHAR-EOF chmod 444 partexp1.sh if [ `wc -c partexp2.sh <<'SHAR-EOF' X#!/bin/ksh X# X# "@(#)partexp2.sh 1.1 93/02/27" X# X# Run parts explosion Mk II X X# This parts explosion produces the picking list of elementary X# components needed to build 1 unit of any particular assembly. X# It lists part names as well as part numbers. X Xtmp=${TMPDIR:-/tmp}/mk2.$$ Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15 Xp1=${tmp}.1 Xp2=${tmp}.2 Xp3=${tmp}.3 Xp4=${tmp}.4 Xp5=${tmp}.5 X Xcat >$p1 <<'EOF' X XSELECT * X FROM bom X INTO TEMP t1; X XSELECT "Parts unexpanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1; X XEOF X Xcat >$p2 <<'EOF' X XSELECT p1, p2, qty X FROM t1 X WHERE p2 NOT IN (SELECT p1 FROM bom) XUNION ALL XSELECT t1.p1, bom.p2, SUM(bom.qty * t1.qty) qty X FROM bom, t1 X WHERE t1.p2 = bom.p1 X GROUP BY 1, 2 XINTO TEMP t0; X XDROP TABLE t1; X XSELECT p1, p2, SUM(qty) qty X FROM t0 X GROUP BY p1, p2 X INTO TEMP t1; X XDROP TABLE t0; X XSELECT "Parts unexpanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1; X XEOF X Xcat >$p3 <<'EOF' XSELECT t1.qty, p1.pno p1n, p1.pname p1, X p2.pno p2n, p2.pname p2 X FROM parts p1, parts p2, t1 X WHERE t1.p1 = p1.pno X AND t1.p2 = p2.pno X ORDER BY p1, qty, p2; XEOF X Xcat >$p4 <<'EOF' XSELECT "--", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 XEOF X Xiterations="-e 'SET EXPLAIN ON'" Xiterations="" X((i=1)) Xwhile ((i<10)) Xdo X echo "`date`: Level $i" X { X echo "\nLevel $i" X eval sqlcmd "-D' '" -d parts $p1 $iterations $p4 $p3 X } >$p5 X grep "^--[ ]*0$" $p5 >/dev/null && break X iterations="$iterations $p2" X ((i=i+1)) Xdone Xecho "`date`: Done" X Xcat $p5 | Xawk -F' ' '# X/^--/ { X printf("\n"); X printf("%3s %-32s %-37s\n", X "Qty", "Assembly", "Component"); X printf("%3s %-32s %-37s\n", X "---", "--------", "---------"); X next; X } X { X if (NF != 5) X print; X else X { X if ($2 != old) X { X print ""; X old = $2; X } X else X { X $2 = ""; X $3 = ""; X } X printf("%3d %-3s %-28s %-3s %-28s\n", X $1, $2, $3, $4, $5); X } X }' X Xrm -f ${tmp}.* SHAR-EOF chmod 444 partexp2.sh if [ `wc -c partexp3.sh <<'SHAR-EOF' X#!/bin/ksh X# X# "@(#)partexp3.sh 1.2 93/02/27" X# X# Run parts explosion Mk II X X# This parts explosion produces the picking list of components needed X# to build 1 unit of any particular assembly, but the list is layered X# so that you get the information about the components needed for the X# assemblies at each level. X Xtmp=${TMPDIR:-/tmp}/mk2.$$ Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15 Xp1=${tmp}.1 Xp2=${tmp}.2 Xp3=${tmp}.3 Xp4=${tmp}.4 Xp5=${tmp}.5 X Xcat >$p1 <<'EOF' XSELECT *, 1 level X FROM bom X INTO TEMP t1; X XSELECT "Parts unexpanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 X AND t1.level = (SELECT MAX(level) FROM t1); XEOF X Xcat >$p2 <<'EOF' XSELECT p1, p2, qty, level X FROM t1 XUNION ALL XSELECT t1.p1, bom.p2, SUM(bom.qty * t1.qty) qty, t1.level + 1 level X FROM bom, t1 X WHERE t1.p2 = bom.p1 X AND level = (SELECT MAX(level) FROM t1) X GROUP BY 1, 2, 4 XINTO TEMP t0; X XDELETE FROM t1; X XINSERT INTO t1 X SELECT p1, p2, SUM(qty) qty, level X FROM t0 X GROUP BY p1, p2, level; X XDROP TABLE t0; X XUPDATE STATISTICS FOR TABLE t1; X XSELECT "Parts unexpanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 X AND t1.level = (SELECT MAX(level) FROM t1); XEOF X Xcat >$p3 <<'EOF' XSELECT t1.qty, p1.pno p1n, p1.pname p1, p2.pno p2n, p2.pname p2, t1.level X FROM parts p1, parts p2, t1 X WHERE t1.p1 = p1.pno X AND t1.p2 = p2.pno X ORDER BY p1, level, qty, p2; XEOF X Xcat >$p4 <<'EOF' XSELECT "--", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 X AND t1.level = (SELECT MAX(level) FROM t1); XEOF X Xiterations="-e 'SET EXPLAIN ON'" Xiterations="" X((i=1)) Xwhile ((i<10)) Xdo X echo "`date`: Level $i" X { X echo "\nLevel $i" X eval sqlcmd "-D' '" -d parts $p1 $iterations $p4 $p3 X } > $p5 X grep "^--[ ]*0$" $p5 >/dev/null && break X iterations="$iterations $p2" X ((i=i+1)) Xdone Xecho "`date`: Done" X Xcat $p5 | Xawk -F' ' '# X/^--/ { X printf("\n"); X printf("%5s %5s %-32s %-32s\n", X "Level", "Qty", "Assembly", "Component"); X printf("%5s %5s %-32s %-32s\n", X "-----", "---", "--------", "---------"); X next; X } X { X if (NF != 6) X print; X else X { X if ($2 != old2) X { X print ""; X print ""; X old2 = $2; X old6 = $6; X } X else if ($6 != old6) X { X print ""; X old6 = $6; X } X printf("%5d %5d %-4s %-27s %-4s %-27s\n", X $6, $1, $2, $3, $4, $5); X } X }' X Xrm -f ${tmp}.* SHAR-EOF chmod 444 partexp3.sh if [ `wc -c partexp4.sh <<'SHAR-EOF' X#!/bin/ksh X# X# "@(#)partexp4.sh 1.1 93/02/27" X# X# Run parts explosion Mk II X X# This parts explosion produces the picking list of components needed X# to build 1 unit of any particular assembly, but the list is layered X# so that you get the information about the components needed for the X# assemblies at each level, and a record of which sub-assembly each X# set of components is destined for. X Xtmp=${TMPDIR:-/tmp}/mk2.$$ Xtrap "rm -f ${tmp}.*; exit 1" 1 2 3 13 15 Xp1=${tmp}.1 Xp2=${tmp}.2 Xp3=${tmp}.3 Xp4=${tmp}.4 Xp5=${tmp}.5 X Xcat >$p1 <<'EOF' XSELECT p1, p1 expof, p2, qty, 1 level X FROM bom X INTO TEMP t1; X XSELECT "Parts unexpanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 X AND t1.level = (SELECT MAX(level) FROM t1); XEOF X Xcat >$p2 <<'EOF' XSELECT p1, expof, p2, qty, level X FROM t1 XUNION ALL XSELECT t1.p1, t1.p2 expof, bom.p2, SUM(bom.qty * t1.qty) qty, t1.level + 1 level X FROM bom, t1 X WHERE t1.p2 = bom.p1 X AND level = (SELECT MAX(level) FROM t1) X GROUP BY 1, 2, 3, 5 XINTO TEMP t0; X XDELETE FROM t1; X XINSERT INTO t1 X SELECT p1, expof, p2, SUM(qty) qty, level X FROM t0 X GROUP BY p1, expof, p2, level; X XDROP TABLE t0; X XUPDATE STATISTICS FOR TABLE t1; X XSELECT "Parts unexpanded", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 X AND t1.level = (SELECT MAX(level) FROM t1); XEOF X Xcat >$p3 <<'EOF' XSELECT t1.qty, p1.pno p1n, p1.pname p1, X p2.pno p2n, p2.pname p2, t1.level, t1.expof X FROM parts p1, parts p2, t1 X WHERE t1.p1 = p1.pno X AND t1.p2 = p2.pno X ORDER BY p1, level, expof, qty, p2; XEOF X Xcat >$p4 <<'EOF' XSELECT "--", COUNT(*) X FROM t1, bom X WHERE t1.p2 = bom.p1 X AND t1.level = (SELECT MAX(level) FROM t1); XEOF X Xiterations="-e 'SET EXPLAIN ON'" Xiterations="" X((i=1)) Xwhile ((i<10)) Xdo X { X echo "\nLevel $i" X eval sqlcmd "-D' '" -d parts $p1 $iterations $p4 $p3 X } >$p5 X grep "^--[ ]*0$" $p5 >/dev/null && cat $p5 && break X iterations="$iterations $p2" X ((i=i+1)) Xdone | X Xawk -F' ' '# X/^--/ { X printf("\n"); X printf("%3s %3s %-32s %-37s\n", X "Lvl", "Qty", "Assembly", "Component"); X printf("%3s %3s %-32s %-37s\n", X "---", "---", "--------", "---------"); X next; X } X { X if (NF != 7) X print; X else X { X if ($2 != old) X { X print ""; X old = $2; X } X else X { X $2 = ""; X $3 = ""; X } X printf("%3d %3d %-3s %-28s %-3s %-3s %-28s\n", X $6, $1, $2, $3, $7, $4, $5); X } X }' X Xrm -f ${tmp}.* SHAR-EOF chmod 444 partexp4.sh if [ `wc -c pp.1.sh <<'SHAR-EOF' X-- @(#)pp.1.sh 1.1 93/02/27 X-- @(#)Transitive closure of PP X XSELECT * X FROM pp X INTO TEMP t1; X X-- Start repeat (Cycle A) XSELECT major_p, minor_p X FROM t1 XUNION XSELECT t1.major_p, pp.minor_p X FROM pp, t1 X WHERE t1.minor_p = pp.major_p XINTO TEMP t0; X XDROP TABLE t1; X X-- This count does not change when sufficient iterations have been done XSELECT "List size", COUNT(*) FROM t0; X-- End repeat (Cycle A) X X-- Start repeat (Cycle B) XSELECT major_p, minor_p X FROM t0 XUNION XSELECT t0.major_p, pp.minor_p X FROM pp, t0 X WHERE t0.minor_p = pp.major_p XINTO TEMP t1; X XDROP TABLE t0; X X-- This count does not change when sufficient iterations have been done XSELECT "List size", COUNT(*) FROM t1; X-- End repeat (Cycle B) X XHEADINGS ON; XDELIM "\t"; XSELECT * FROM t1 ORDER BY major_p, minor_p; SHAR-EOF chmod 444 pp.1.sh if [ `wc -c pp.sql <<'SHAR-EOF' X-- @(#)pp.sql 1.1 93/02/27 X-- @(#)Table and Data for Part Explosion Experiments X XCREATE TABLE pp X( X major_p CHAR(2) NOT NULL, X minor_p CHAR(2) NOT NULL X); X XINSERT INTO pp (major_p, minor_p) VALUES ("P1", "P2"); XINSERT INTO pp (major_p, minor_p) VALUES ("P1", "P3"); XINSERT INTO pp (major_p, minor_p) VALUES ("P1", "P4"); XINSERT INTO pp (major_p, minor_p) VALUES ("P2", "P3"); XINSERT INTO pp (major_p, minor_p) VALUES ("P2", "P5"); XINSERT INTO pp (major_p, minor_p) VALUES ("P3", "P5"); XINSERT INTO pp (major_p, minor_p) VALUES ("P3", "P6"); XINSERT INTO pp (major_p, minor_p) VALUES ("P4", "P3"); XINSERT INTO pp (major_p, minor_p) VALUES ("P4", "P6"); XINSERT INTO pp (major_p, minor_p) VALUES ("P7", "P8"); XINSERT INTO pp (major_p, minor_p) VALUES ("P7", "P9"); SHAR-EOF chmod 444 pp.sql if [ `wc -c