Query to retrieve Entity List from ER_Diagram
Hi all
I was running a query to retrieve entity list from a ER diagram (display area) under a subject area. As per my knowledge the hierarchy is Model has many subject areas and each subject area has many ER Diagrams . I tried the below query to list super types for a ER Diagram under a subject area
SELECT distinct
ed.name,s.name,
tran (LEnP.Name) 'Parent Entity Name',
tran (LEnP.User_Formatted_Physical_Name) 'Parent Table Name',
tran (LEnC.Name) 'Child Entity Name',
tran (LEnC.User_Formatted_Physical_Name) 'Child Table Name',
tran (LRel.Type) 'Relationship Type'
FROM
Relationship LRel
INNER JOIN Entity LEnP
ON LRel.Parent_Entity_Ref = LEnP.Id@
INNER JOIN Entity LEnC
ON LRel.Child_Entity_Ref = LEnC.Id@
INNER JOIN M0.USER_ATTACHED_OBJECTS_REF saf
ON LEnP.id@=saf.value@
INNER JOIN M0.Subject_Area s
ON s.id@=saf.id@
INNER JOIN ER_DIAGRAM ed
on s.id@=ed.owner@
where tran(LRel.TYPE) = 'Subtype' and s.name ='Visualization' and ed.name='ER_Diagram_14818'
ORDER BY 1,3
It shows list of subtypes even if no entity is populated in ER_Diagram_14818 ( The subject area has list of entities populated to other ER Diagrams e.g. Logical/Physical shown in the picture above ). The Question is how to get the list of entities displayed under an ER_Diagram
Can you help
Ani
-
Ari,
Be careful. Subtype relationships always link to a Subtype Symbol and an entity. The parent entity of the Subtype Symbol is the supertype and the child entities of the Subtype Symbol are the subtypes.
In your query you navigate from Parent Entity to Subject Area. This will automatically include all the entities you have selected so far. When you go from Subject Area to ER Diagram all the entities you have selected so far will still be included in the result set. You need to go from Parent Entity to ER Diagram to Subject Area to get the subset you want.
Disclaimer: I do not have ERwin and can't test anything. I also don't have metadata for ER Diagram so the following query is undoubtedly wrong but it might provide insight for a different approach.
select subject_area.Name 'Subject Area'
,er_diagram.Name 'ER Diagram'
,supertype_entity.Name 'Supertype Entity Name'
,supertype_entity.User_Formatted_Physical_Name 'Supertype Table Name'
,subtype_entity.Name 'Subtype Entity Name'
,subtype_entity.User_Formatted_Physical_Name 'Subtype Table Name'from M0.Subject_Area
join ER_Diagram
on subject_area.Id@ = er_diagram.Owner@
and er_diagram.Name = 'ER_Diagram_14818'join M0.Referenced_Entities_Ref
on er_diagram.Id@ = referenced_entities_ref.Id@join M0.Subtype_Symbol
on referenced_entities_ref.Value@ = subtype_symbol.Id@join M0.Relationship parent_relationship
on subtype_symbol.Id@ = parent_relationship.Child_Entity_Refjoin M0.Entity supertype_entity
on parent_relationship.Parent_Entity_Ref = supertype_entity.Id@join M0.Relationship child_relationship
on subtype_symbol.Id@ = child_relationship.Parent_Entity_Refjoin M0.Entity subtype_entity
on child_relationship.Child_Entity_Ref = subtype_entity.Id@where subject_area.Name = 'Visualization'
order by supertype_entity.Name
,subtype_entity.Name
If this doesn't work try replacing M0.Referenced_Entities_Ref with M0.User_Attached_Objects_Ref.
Hope this helps,
--mls
Note: the Relationship Navigation subject area in the attached metamodel illustrates how it worked a few years back.
-
Ani,
You are certainly welcome for the meta model link.
You might try altering your original query to include ER Diagram in the filter sequence:
original query snippet:
INNER JOIN M0.Subject_Area s
ON s.id@=saf.id@
INNER JOIN ER_DIAGRAM ed
on s.id@=ed.owner@change to:
INNER JOIN ER_DIAGRAM ed
ON ed.id@=saf.id@
INNER JOIN M0.Subject_Area s
on s.id@=ed.owner@If this doesn't work it means that both M0.User_Attached_Objects_Ref and M0.Referenced_Entities_Ref only connect objects to subject areas and there are one or more other vector tables to specify objects in er diagrams.
I used to have a query to find vector tables and their referents. This might be it:
select Name ,tran(Participating_Object_Ref) 'Vector Attribute Owner (Id@)' ,tran(Referenced_Type_Ref) 'Referenced type (Value@)' from M1.Property_Type join M1.Association_Type on Id@ =Participating_Property_Ref where Tag_Is_Reference = 'T' and tran(Data_Type) = 'Short Id Vector' order by Name ,tran(Participating_Object_Ref)
If this actually works it should give you a list of objects with vector attributes. Look for er diagram somewhere in the list of Vector Attributes Owner.
--mls
Column
Please sign in to leave a comment.
Comments
3 comments