Does With Recursive lc as ( Select ID from BD_Locality where ID = :ID_Root Union all Select ID from BD_Locality, lc where Parent_ID = lc.id ),
RPL (BizType_Description, Loc_Description) as ( select BizType_Description, Loc_Description from v_bd_biz_prim where BizType_ID = :BizType_ID and (Loc_ID = :ID_root or Parent_Loc_ID = :ID_root) and Loc_ID in (Select ID from lc)) Union all Select BizType_Description, Loc_Description from V_BD_Biz_Prim where ID > 0 and BizType_ID = :BizType_ID Union all Select BizType_Description, Loc_Description from V_BD_Biz_Sec_OtherUnits where ID > 0 and BizType_ID = :BizType_ID ) Select BizType_Description, Loc_Description, Count(*) as Nr_Records from RPL Group by BizType_Description, Loc_Description give you what you're looking for, Nols? By the way, there are two things I would consider changing. I've never actually seen SQL-89 type JOINs with recursive CTEs before. Moreover, not qualifying the tables, ID is ambiguous (do you mean to select 'lc.ID' or 'bd_locality.ID' after UNION ALL? Of course, semantically, only bd_locality.ID would make any sense, but syntactically it is ambiguous). I'd recommend changing to With Recursive lc as ( Select b1.ID from BD_Locality b1 where b1.ID = :ID_Root Union all Select b2.ID from BD_Locality b2 join lc l on b2.Parent_ID = l.id ) The other bit is that using IN (subselect) used to be very slow (being executed for every potential row). It has been considerably improved, though I don't know whether it has been fixed in all circumstances. If lc.ID is unique, then consider changing to select p.BizType_Description, p.Loc_Description from v_bd_biz_prim p join lc l on p.Loc_ID = l.ID where p.BizType_ID = :BizType_ID and (p.Loc_ID = :ID_root or p.Parent_Loc_ID = :ID_root) HTH, Set
