Re: Chasing the tail

} Date: Thu, 12 Jan 1995 07:51:09 +1100 (EST) } From: Peter Harris <karnak@jolt.mpx.com.au> } Subject: Chasing the tail } To: informix-list@rmy.emory.edu } } Hi all, } I have got one here that is making me wonder: I am trying to } squeeze information out of a commercial accounting package, Bill of } Materials Header and Detail. So far so good, the catch is that the Detail } table is self referencing: components go to make up components etc until } you get the finished part. } } Header ( header_key char(20), other stuff about the finished part.. ) } } Detail ( detail_key char(20), component_key char(20) <-references a } sub_component's } detail_key ) } } The detail table holds costing information which I need to sum to get a } cost per unit for each part in the header table. Easy? } } I tried building a reference table, with each detail component listed } against what part in the header it ultimately goes to make up. The } mathematics of permutations and combinations made me fall off the edge } of the disk. } } I tried recursively following the detail_key, component_key, } detail_key... trail for each finished part and had a program that took } days to run. } } Anybody come across this sort of problem before? bright ideas? Am I } missing the obvious? } } All the best, Pete. It sounds like your database has a circular reference in the Detail-Detail linkages. This might be valid if a component has alternate configurations, one of which logically ends the recursion. Otherwise, your DB is in deep kimchee. Assuming no circular links, then your query should terminate long before "days" of running. Whether your disk gets full is a function of DB size vs. free disk space. Now if your DB does include valid alternate component configurations, some of which (circularly) reference components "higher" in the tree, then you need to write a "smart" parts explosion report that knows how to end the recursion. Straight SQL is normally not smart enough to do this. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\