467,189 Members | 1,304 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

Set level acestor report; DB2 UDB 8.1.9 Linux

From a table like the one below, is there an easy (ha ha) way to get a
set level of pedigree (3 or 5 generations, say?). I have seen solutions
on this group for all the ancestors, but, unless I misunderstood, no
solution for a fixed number of generations. There is always the 2^n way
join for n generations, but I'm looking for something a little simpler
and not so fast growing in term of n. To make things easier? animal 0 is
its own parents, and the parent of all animals whose parent(s) is(are)
unknown. Right now, I am solving the three generation problem with a
recursive php function, but I am looking for a non-procedural sql solution.
create table t (
animal_id integer,
sire_id integer,
dam_id integer,
namex varchar(40),
<lots of other stuff>,
primary key (animal_id) )
alter table t
add constraint sire_id
foreign key(sire_id)
references t(animal_id)
alter table t
add constraint dam_id
foreign key(dam_id)
references t(animal_id)
Mar 29 '06 #1
  • viewed: 1198
Share:
2 Replies
In article <at*************@fe03.lga>, rs**********@charter.net says...
From a table like the one below, is there an easy (ha ha) way to get a
set level of pedigree (3 or 5 generations, say?).


One of my favorite replies :)

Download (and read) the DB2 UDB Cookbook from Graeme Birchall (page 299,
recursive queries) from
http://mysite.verizon.net/Graeme_Birchall/id1.html

Mar 29 '06 #2
Gert van der Kooij wrote:
In article <at*************@fe03.lga>, rs**********@charter.net says...
From a table like the one below, is there an easy (ha ha) way to get a
set level of pedigree (3 or 5 generations, say?).

One of my favorite replies :)

Download (and read) the DB2 UDB Cookbook from Graeme Birchall (page 299,
recursive queries) from
http://mysite.verizon.net/Graeme_Birchall/id1.html

Thank you for the reply/pointer.

While I only read the recommended chapter, I would recommend it to any
one using DB2. It is clearly written, with copious examples.

The given solutions, as I read the chapter, contain 1 row for each
individual in the ancestor tree. What if I want 1 row for the scion with
all of the ancestors, perhaps in some given order?
Mar 29 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by eliffman | last post: by
44 posts views Thread by John Dann | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.