Re: Chasing the tail
Thanks to jenb, Jack Parker & Alan Popiel.
What I ended up doing was a series of outer joins from the detail
table to itself. It looked a bit like this:
select header.header_key, header.description,
level_1.detail_key, level_1.cost_per_unit, level_1.component_key,
level_2.detail_key, level_2.cost_per_unit, level_2.component_key,
level_3.detail_key, level_3.cost_per_unit, level_3.component_key,
...
from header, detail level_1,
outer ( detail level_2,
outer ( detail level_3,
...
...))While this is hard coding the levels of sub-components, is this case this was
a fixed system wide parameter determined by the application, so I could
get away with it. Like all these things, the answer was obvious when I
stopped thinking about it!
On Thu, 12 Jan 1995, Peter Harris wrote:
}
} 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.
}