Hi,

Thanks a lot for the help.

I now have the following working, except for the one-to-many relationships (two of them).

With Recursive r1 as
(
Select t1.ID from BD_Locality t1
where t1.ID = :ID_RootLoc
Union all
Select t2.ID from BD_Locality t2
join r1 j1 on t2.Parent_ID = j1.id
)
,

r2 as
(
Select t3.ID from CGS_Structure t3
where t3.ID = :ID_RootCGS
Union all
Select t4.ID from CGS_Structure t4
join r2 j2 on t4.Parent_ID = j2.id
)

select p.biztype_description, p.service_description, p.unit_description, p.loc_description, p.status_description, count(*) as Nr_Records from v_bd_biz_prim p
join r1 jl on p.Loc_ID = jl.ID
join r2 j2 on p.CGS_ID = j2.ID
where p.BizType_ID = :BizType_ID
Group by p.biztype_description, p.service_description, p.unit_description, p.loc_description, p.status_description


Regarding one of the one-to-many relationships:
Certain records in the view v_bd_biz_prim, are foreign keys in a table named BD_Biz_OtherUnits (collaborative units on business initiatives). The foreign key is named Biz_ID.

I would like to also locate, select, count and group these records, via above-mentioned recursive CTE, by specifying their key-values for the parameter ID_RootCGS

The other one-to-many relationship refers to business partners but it will be handled the same way.



Regards,

Nols Smit




[Non-text portions of this message have been removed]