---------- Original Message -----------
From: "tstayfun" <tstayfun@...>

> Can i select detail tables like this???
>
> M.Tmp, D1.Tmp1, D2.Tmp2
> A D1A1 AAA1
> A D1A2 AAA2
> A <null> AAA3
> B D1B1 BB1
> B D1B2 BB2
> B D1B3 <null>

------- End of Original Message -------

We won't have DENSE RANK until FB 3.0, so it's hard to do an outer join based
on tmp1.rank = tmp2.rank ... and it probably wouldn't be efficient anyway. So
here's my solution. Fun exercise. I've not had to use pre-declared cursors
before, but that seemed required for doing this sort of master-detail "walk".
Haven't had to write this algorithm since that COBOL class ... *shiver* And it
may still have holes. I tested it with an extra master row with no details, but
I didn't try all permutations. Happy testing!

Hope this helps.

-Philip

set term ^;
execute block returns (tmp varchar(10), tmp1 varchar(10), tmp2 varchar(10)) as
declare variable m0 integer; -- current row from each table
declare variable m1 integer;
declare variable m2 integer;
declare variable c0 cursor for (select mrec, tmp from testmaster order by mrec
asc);
declare variable c1 cursor for (select mrec, tmp1 from testdetail1 order by
mrec asc);
declare variable c2 cursor for (select mrec, tmp2 from testdetail2 order by
mrec asc);
declare variable t1 varchar(10); -- fetched value, might be for next master,
keep in reserve until appropriate
declare variable t2 varchar(10);
declare variable u1 integer = 0; -- row used, need to advance
declare variable u2 integer = 0;
declare variable s0 integer = 0; -- row fetched, needs to be suspended
eventually
begin
open c0;
open c1;
open c2;

while (1=1) do
begin
-- last time, we didn't get anything interesting, so advance
if ((m1 is null or m1 > m0) and (m2 is null or m2 > m0)) then
begin
s0 = 1;
fetch c0 into m0, tmp;
if (row_count = 0) then leave;
end else
s0 = 0;

while (u1 = 1 or m1 is null or m1 < m0) do
begin
u1 = 0;
fetch c1 into m1, t1;
if (row_count = 0) then
begin
m1 = null;
t1 = null;
leave;
end
end

while (u2 = 1 or m2 is null or m2 < m0) do
begin
u2 = 0;
fetch c2 into m2, t2;
if (row_count = 0) then
begin
m2 = null;
t2 = null;
leave;
end
end

if (m1 = m0 or m2 = m0) then
begin
if (m1 = m0) then
begin
tmp1 = t1;
u1 = 1;
end else
tmp1 = null;
if (m2 = m0) then
begin
tmp2 = t2;
u2 = 1;
end else
tmp2 = null;
s0 = 0;
suspend;
end
else if (s0 = 1) then
begin
tmp1 = null;
tmp2 = null;
s0 = 0;
suspend;
end
end
close c0;
close c1;
close c2;
end^
set term ;^