Did anyone have any thoughts? On Mon, 2006-09-18 at 00:02 -0400, THO wrote: > In need of suggestions to get correct part multiplier factor. > (apologies up front for message length) > > I have an parts/assemblies table. > > mysql> describe assycard; > +----------+-----------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+-----------------+------+-----+---------+-------+ > | part_id | int(7) unsigned | NO | PRI | 0 | | > | assy | int(7) | NO | PRI | 0 | | > | qty_assy | int(7) unsigned | NO | | 0 | | > | unit | char(10) | NO | | EA | | > +----------+-----------------+------+-----+---------+-------+ > > This table forms an assembly tree and parts list resource. qty_assy is > the qty of each part (part_id) that belongs to an assembly (assy). > Assembly number assy can also be a part_id and in that case is called a > sub-assembly and has a qty_assy value for how many sub-assemblies belong > to its parent assembly. > > I have a procedure that eventually, properly generates the tree... > Thanks to info from > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html > > What I have been unable to get right is the qty_assy multipliers up the > tree. > > truncated example; > > +---------+--------------------------------+-------------+ > | part_id | assy | qty_assy | > +---------+--------------------------------+-------------+ > | 8100028 | 8100030 | 8 | > | 8100029 | 8100028 | 6 | > | 1000100 | 8100029 | 1 | > | 2100050 | 8100029 | 4 | > +---------+--------------------------------+-------------+ > > parts 1000100 and 2100050 belong to assembly 8100029 which is a child of > 8100028, 8100028 is a child of the top assembly 810030. In this example > there are 6 8100029's in 8100028 and 8 8100028's in 8100030 therefore > every part in 8100029 should be multiplied by 48 to account for all > parts from 8100029 included in 1 8100030 (48 1000100's and 192 > 2100050's). Parts in 8100028 should only be multiplied by 8 to account > for parts in 8100028 included in 8100030. The actual depth of the tree > is in principal unknown so I need to walk the tree and correctly collect > the multipliers for each assembly. > > The heart of the assembly tree procedure that works, with some cleaning > up of the result, is below but carrying the qty factors along the way > did not work... > > DROP PROCEDURE IF EXISTS ListDescendants; > DELIMITER | > CREATE PROCEDURE scanner.ListDescendants( IN ancestor int(7) ) > BEGIN > DECLARE rows INT DEFAULT 0; > -- CREATE NEEDED TABLES > DROP TABLE IF EXISTS descendants; > CREATE TABLE IF NOT EXISTS descendants ( d_part_id int(7), d_assy > int(7), d_qty int(5) ) ENGINE=MEMORY; > DROP TABLE IF EXISTS nextparents; > CREATE TABLE IF NOT EXISTS nextparents ( assy int(7), np_qty_assy > int(5) ) ENGINE=MEMORY; > DROP TABLE IF EXISTS prevparents; > CREATE TABLE prevparents LIKE nextparents; > > -- SEED NEXTPARENTS > INSERT INTO nextparents > SELECT assy, qty_assy FROM assycard WHERE assy=ancestor and > qty_assy!=0; > SET rows = ROW_COUNT(); > > WHILE rows > 0 DO > -- ADD CHILDREN OF NEXTPARENTS > INSERT INTO descendants > SELECT t.part_id, t.assy, t.qty_assy > FROM assycard AS t > INNER JOIN nextparents USING(assy) where t.part_id!=0; > SET rows = ROW_COUNT(); > -- SAVE COPY OF NEXTPARENTS > TRUNCATE prevparents; > INSERT INTO prevparents > SELECT * FROM nextparents; > -- NEXT PARENTS ARE CHILDREN OF THESE PARENTS: > TRUNCATE nextparents; > INSERT INTO nextparents > SELECT part_id, qty_assy FROM assycard > INNER JOIN prevparents USING (assy) where assycard.part_id!=0; > SET rows = rows + ROW_COUNT(); > END WHILE; > ... > cleanup of resulting descendants table, joining with part and cost > information from other tables... >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
