By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Set level acestor report; DB2 UDB 8.1.9 Linux

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.