From: wengland@stephsf.com (Bill England)
Subject: Re: parts explosion (Sybase Guru)
Date: Mon, 6 Apr 1992 23:26:19 GMT
Organization: Stephen Software Systems Inc., Tacoma/Seattle, +1 800 829 1684
X-Bytes: 3072

In article <1992Mar28.155645.2168@baarsa.bell-atl.com> dd6kdn0@baarsa.bell-atl.com (Maurer) writes:
>I would like suggestions of the best way to 
>implement a parts explosion (leafs of tree only)
>in Sybase transact-sql.
>
>I have a table with two objects:
>
>	parent_obj
>	child_obj

  Recursive  data structures are difficult in 
  SQL's because cursor definitions are globaly and
  not localy defined.  This means that you can't define
  a cursor twice (At least not without closing the previous 
  occurance of the cursor.)

  My first reaction is to keep it simple and define one
  table that relates to it's self as in:

  part = part_key+ part_parent_key+ other_stuff


  This is a very simple representation and relatively
  easy to implement.  To build the recursive routines
  using cursors you would need to have a local array
  that kept all child parts that resembled: 

------
# The following is silly code (pseudo code) and does
# not represent any particular parsable language.

global	rg_low_level_parts[MAX_PARTS] partkey_type;
        ttl_sub_parts integer;

ttl_sub_parts = 0;

## recurse_part_list returns number of sub parts found 
## for a part
##
Function recurse_part_list(part_key)
    part_key	part_key_type;
    rg_part_key[MAX_PARTS/2]     parkey_type;

  Declare part_curs Cursor for 
    Select part_key
    From part
    Where part_parent_key = @part_key


  n=0
  Foreach part_curs into rg_part_key[n++]
    If n>= (MAX_PARTS/2)
       print error message and fail
       n = -1
       exit foreach
    End If 
  End Foreach

  If n Then
      For j=1 to n
	 if(num_parts = recurse_part_list(rg_part_key[j])) Then
	    Return -1  if num_parts < 0; # Array too small error return.
	    rg_low_level_parts[ttl_sub_parts++] = rg_part_key[j];
	 End If 
      End For
  End If

  Return n;

End Function
-------

  Actually I've a better method than this and almost as simple.  The
  only problem is that you need to write your code in an embeded sql
  and not in an 4gl.  The other method involves reading all
  parts into an array and then building a cross referance array 
  using a quick sort.  The second method is very fast since
  you only open one cursor. 

  There are some other data representation problems not addressed here 
  however.  Lets say that our database is describing a Case tractor.  
  Many of the parts on the tractor and are exchangeable with like 
  parts on other tractors.  You will need some form of linked list to 
  describe the cross over of parts.  Also, what are you going to do 
  about obsolete parts that are replaced by new parts with different 
  part numbers?

  (I wouldn't be thinking about these except that I just finished
   a part cross referance/pricing system.)


  Regards,


-- 
 +-  Bill England,  wengland@stephsf.COM -----------------------------------+
 |   * *      H -> He +24MeV                                                |
 |  * * * ... Oooo, we're having so much fun making itty bitty suns *       |
 |__ * * ___________________________________________________________________|