| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: PS/SQL for constructing binary tree for pedigree?
James,
Tree structures like the one you describe are easy to map to relational tables. Querying them, however, is typically not easy using straight SQL; procedural constructs (loops) are usually required. Oracle has a proprietary extension to its SELECT syntax, the START WITH and CONNECT BY clauses, that are directly designed for querying hierarchical data. While it has certain restrictions (e.g. joins are not allowed -- stored functions can often be used to get around this limitation), it is a powerful feature worth reading up on.
Gerard
In article <3487FAFD.34BF_at_celltech.co.uk>, jpetts_at_celltech.co.uk wrote:
>I'm about to start writing an Oracle app for a horse
>breed society stud book. One of the things I need to
>do is to be able to construct a pedigree chart going
>back a given number of generations. Thus, each horse
>record will have a mother and a father field, each of
>which references the PK of another horse record. If
>the mother and/or father is unknown the appropriate
>field will be NULL.
>
>What I need is to take as input the maximum number of
>generations to trace back, and construct a binary
>tree of the ancestors of the horse going back that
>number of generations by recursively getting the parents
>of the previous generations.
>
>Before I sit down and try and code something up myself,
>doesn anybody have a routine that will do this sort of
>thing or perhaps a reference for an appropriate algorithm?
>--
>James "I'd rather fall off Ilustrada than ride any other horse" Petts
 |
 |