SQL Query to Return System Parent at Any Depth

x x x x x x x x x

x x x x x x x x x

x x x x x x x x x

SQL Query to Return System Parent at Any Depth

SQL 查询出对象的任意层级深度的父级

x x x x x x

Issue

Query to return the name of a parent object on the Systemhierarchy of a specific object type given the OID of a child object of aspecific object type. The depth of the child below the parent is unknown andmay vary within the model.

 在得到某一种对象的oid之后，使用sql查询返回对象的任一种父级对象的名字。子级对象与父级对象的相对深度在项目模型中不一定。

Environment

Not version specific

 

Resolution

解决方案：

Since the child’s depth below the parent is variable, thesolution requires a SQL query using the function REPORTGetAllParentsInHierarchyfor SQL databases or RPTAllParentsInHierarchy for Oracle databases. The examplebelow returns the name of the parent equipment of a child shape.

 

For SQL databases:

 适用于SQL数据库平台：

select

j2.oid ShapeOID,

j2.ItemName ShapeName,

j4.oid EquipOID,

j4.ItemName EquipName

from JPrimitiveShape j1

join JNamedItem j2 on j2.Oid = j1.Oid

cross applydbo.REPORTGetAllParentsInHierarchy(j1.Oid,’SystemHierarchy’) s1

join JSmartEquipmentOcc j3 on j3.Oid = s1.oidParent

join JNamedItem j4 on j4.Oid = j3.Oid

 

For Oracle databases:

 适用于Oracel数据库平台。

select

j2.oid ShapeOID,

j2.ItemName ShapeName,

j4.oid EquipOID,

j4.ItemName EquipName

from JPrimitiveShape j1

join JNamedItem j2 on j2.Oid = j1.Oid

cross joinTABLE(RPTAllParentsInHierarchy(j1.Oid,’SystemHierarchy’)) s1

join JSmartEquipmentOcc j3 on j3.Oid = s1.oidParent

join JNamedItem j4 on j4.Oid = j3.Oid

 

Cause

Informational