Not being all too happy with using SELECT COUNT in the query (expecting it to be potentially time consuming), I decided to try WITH RECURSIVE again, and to my surprise - now that I'd introduced cted3, it worked:

with RECURSIVE cted1(mrec, tmp1, MyOrder, RecID) as
(select d1.MREC, d1.TMP1, CAST(1 AS INTEGER), d1.RECID
from TESTDETAIL1 d1
where NOT EXISTS(SELECT * FROM TESTDETAIL1 D1B WHERE d1.MREC = d1b.MREC and d1.RECID > d1b.RECID)
union all
select d1.MREC, d1.TMP1, c.MyOrder+1, d1.RECID
from cted1 c
join TESTDETAIL1 d1 on c.MREC = d1.MREC aND c.recid < d1.RECID
where NOT EXISTS(SELECT * FROM TESTDETAIL1 D1B WHERE d1.MREC = d1b.MREC
and c.RECID < d1b.RECID and d1.RECID > d1b.RECID)
)
,
cted2(mrec, tmp2, MyOrder, RECID) as
(select d2.MREC, d2.TMP2, CAST(1 AS INTEGER), d2.RECID
from TESTDETAIL2 d2
where NOT EXISTS(SELECT * FROM TESTDETAIL2 D2B WHERE d2.MREC = d2b.MREC and d2.RECID > d2b.RECID)
union all
select d2.MREC, d2.TMP2, c.MyOrder+1, d2.RECID
from cted2 c
join TESTDETAIL2 d2 on c.MREC = d2.MREC aND c.recid < d2.RECID
where NOT EXISTS(SELECT * FROM TESTDETAIL1 D2B WHERE d2.MREC = d2b.MREC
and c.RECID < d2b.RECID and d2.RECID > d2b.RECID)
)
,
cted3(mrec, TMP1, TMP2) as
(select coalesce(cted1.MREC, cted2.MREC), cted1.TMP1, cted2.TMP2
from cted1
full join cted2 on cted1.MREC = cted2.MREC and cted1.myorder = cted2.myorder)

select m.TMP, d.TMP1, d.TMP2
from TESTMASTER m
join cted3 d on m.MREC = d.MREC

Note that there is a maximum depth to which recursive CTEs work, and using them this way (for which they weren't intended), you may quickly reach this limit.

HTH,
Set

I wrote:

> With a minor change I got it to work (joining the details before getting to the master):
>
> with cted1(mrec, tmp1, MyOrder) as
> (select d1.MREC, d1.TMP1, (select count(*) from TESTDETAIL1 d1b
> where d1.MREC = d1b.MREC and d1.RECID > d1b.RECID)
> from TESTDETAIL1 d1),
> cted2(mrec, tmp2, MyOrder) as
> (select d2.MREC, d2.TMP2, (select count(*) from TESTDETAIL2 d2b
> where d2.MREC = d2b.MREC and d2.RECID > d2b.RECID)
> from TESTDETAIL2 d2),
> cted3(mrec, TMP1, TMP2) as
> (select coalesce(cted1.MREC, cted2.MREC), cted1.TMP1, cted2.TMP2
> from cted1
> full join cted2 on cted1.MREC = cted2.MREC and cted1.myorder = cted2.myorder)
>
> select m.TMP, d.TMP1, d.TMP2
> from TESTMASTER m
> join cted3 d on m.MREC = d.MREC
>
> HTH,
> Set
> ________________________________________
>
> > 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>
> >
> > from the following tables:
> >
> > TESTMASTER
> > MREC TMP
> > 1 A
> > 2 B
> >
> > TESTDETAIL1
> > RECID MREC TMP1
> > 1 1 D1A1
> > 2 1 D1A2
> > 3 1 D1B1
> > 4 1 D1B2
> > 5 1 D1B3
> >
> > TESTDETAIL2
> > RECID MREC TMP2
> > 1 1 AAA1
> > 2 1 AAA2
> > 3 1 AAA3
> > 4 2 BB1
> > 5 2 BB2