Very Computer

by **Peter Harr** » Sat, 14 Jan 1995 00:38:48

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.

by **Alan Popi** » Sat, 14 Jan 1995 03:58:09

} Date: Thu, 12 Jan 1995 07:51:09 +1100 (EST)

} Subject: Chasing the tail

}
} 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.