Query to retrieve Entity List from ER_Diagram



  • Avatar
    Michael Silves


    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_Ref

    join 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_Ref

    join M0.Entity subtype_entity
      on child_relationship.Child_Entity_Ref = subtype_entity.Id@

    where subject_area.Name = 'Visualization'

    order by supertype_entity.Name

    If this doesn't work try replacing M0.Referenced_Entities_Ref with M0.User_Attached_Objects_Ref. 

    Hope this helps,


    Note: the Relationship Navigation subject area in the attached metamodel illustrates how it worked a few years back.


    Comment actions Permalink
  • Avatar


    Thanks I tried the first query, not rows retrieved and tried  replacing M0.Referenced_Entities_Ref with M0.User_Attached_Objects_Ref without luck.

    I will do some R&D and try to get it working

    Thanks a lot for providing me the meta model



    Comment actions Permalink
  • Avatar
    Michael Silves


    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@
    on s.id@=ed.owner@

    change to:

    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

    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. 








    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk