Re: Parts explosion with repeated subtrees

>> Transitive closure should have been part of SQL and SQL based
products ages ago, and probaby it's not yet, but can be solved relatively painful using DB2 recursive queries, Oracle CONNECT BY operator, or using hand made "materialized view" tables updated via triggers. <<

Actually, the WITH operator you see in DB2 is taken from the SQL-99 Standard
and gives us a nice way to hide repeated UNIONs that buyild a transitive closure. The Oracle CONNECT BY is a hidden cursor and it really stinks; it is not even a good kludge.

>> Joe, you have to confess that you orchestrated this whole thing, so
that
you can publish a whole book full of twisted and mind boggling Joe Celko style SQL, solving a problem that in a normal world would be absolutely trivial :)<<

NAH!!! I am trying to get *other people* to write a whole book full of twisted and mind boggling Joe Celko style SQL for me!!

Seriously, I can come up with a full parts explosion in a tree, if I repeat the explosions of all occurences of common subassemblies. But I keep thinking that there should be a neat, clean, simple way to have one tree for each common subassembly and somehow copy it into the full explosion.

I can find the common subassemblies with an exhaustive search:

1. Go to each node, find the size of the subtree rooted at that node, where size = (rgt-lft+1)/2
2. Compare the nodes in each pair of samed-size subtrees.
3. If two subtrees have the same set of nodes, then compare the structure of the two subtrees.
4. If the two subtrees have the same set of nodes and the same structure, then they are the same subassembly.

But should I put them into another table, give the subassembly a name and use that name in the original parts explosion? Received on Mon Dec 16 2002 - 17:39:08 CET