How to Create an Advanced Query that populates Class Display Label


Applies To 
Product(s):Engineering Data Manager
Version(s):

Environment: Windows 10 (64-bit)
Area: Query
Subarea: Advanced Query
Original Author:Fer Mykiel Gonzales, Bentley Product advantage Group

Overview

User wants to create a valve report that will show the Class Display Label such as Gate Valve, Control Valve, and so on. The report will be done using Advanced Query. 

Steps to Accomplish

    1. Open your PlantSight project 
    1. Open Tag Management


    2. Go to Query Browser


    3. Define a Query and toggle the Advanced Query


    4. Add the following in the Query section:


      SELECT va.codevalue AS [Valve Tag],
      GROUP_CONCAT(DISTINCT(IFNULL(va.model_number, '-'))) AS [Valve Code], GROUP_CONCAT(DISTINCT(IFNULL(se.codevalue, '-'))) AS [Pipe Run], GROUP_CONCAT(DISTINCT(IFNULL(sy.specification, '-'))) AS [Spec],
      class.DisplayLabel AS [Valve Class],
      GROUP_CONCAT(DISTINCT(IFNULL(sy.codevalue, '-'))) AS [Pipeline Name],
      GROUP_CONCAT(DISTINCT(IFNULL(do.CodeValue, '-'))) AS [PID No],
      GROUP_CONCAT(DISTINCT(IFNULL(se.size, '-'))) AS [Size]
      FROM pfunc.INLINE_VALVE va
      LEFT JOIN pfunc.DOCUMENT_IS_RELATED_TO_OBJECT re ON va.EcInstanceID = re.TargetECInstanceID
      LEFT JOIN pfunc.PID_DOCUMENT do ON re.SourceECInstanceID = do.ECInstanceID
      LEFT JOIN pfunc.SEGMENT_HAS_PIPING_COMPONENT re1 ON va.EcInstanceID = re1.TargetECInstanceID
      LEFT JOIN pfunc.PIPING_NETWORK_SEGMENT se ON re1.SourceECInstanceID = se.ECInstanceID
      LEFT JOIN pfunc.PIPELINE_HAS_SEGMENT re2 ON se.EcInstanceID = re2.TargetECInstanceID
      LEFT JOIN pfunc.PIPING_NETWORK_SYSTEM sy ON re2.SourceECInstanceID = sy.ECInstanceID
      LEFT JOIN meta.ecclassdef class ON va.ECClassID = class.ECInstanceId
      WHERE va.codevalue IS NOT NULL
      GROUP BY va.codevalue
      ORDER BY va.codevalue


      Note: The Highlighted Texts above are required to pull the Class Display Label. The LEFT JOIN meta.ecclassdef class ON va.ECClassID = class.ECInstanceId gets the Class Name of the INLINE_VALVE class (va) and matches this to the ECClassdef table.

    5. Save As 


    6. Enter the Query Name and click OK


    7. Check the Query Output

    Still a Problem?

    If the above problem persists, then please create a Service Request and contact the Bentley Product Advantage group.  Please include a list of all relevant details to get in depth analysis for a solution or workaround.