SQL query for searching responsible person group for classification looking from bottom level to top level classification tree

If you want to define common responsible person group for all child classification elements hierarchy from top to down element, by defining responsible person group only for one top classification element, you can use the following sql query to define relationship for the object (e.g. TICKET, WORKORDER etc., all objects which has classstructureid attrribute) with persongroup object.



SQL where clause for relationship with persongroup object:

persongroup in (
select top 1 classstructure.persongroup from classancestor
join classstructure on classancestor.ancestor = classstructure.classstructureid and persongroup is not NULL
where classancestor.classstructureid = :classstructureid
order by classancestor.hierarchylevels)