To Customize Query Results in Engineering Data Manager



Applies To 
Product(s):PlantSight
Version(s):
Environment: Windows 10/11 (64 bit)
Area: Engineering Data Manager
Subarea: Queries
Original Author:Álvaro Melgarejo, Bentley Global Technical Support

Overview

In this wiki article, two ways of how to write a SQL query will be outlined in order to get desired qualities in the resulting tables.

Problem Description

When querying an i-model in Engineering Data Manager, the user might want to customize the resulting tables. Below, it will be shown (1) how to query the i-model so as to get two fields combined into a single one and (2) how to change the displayed name of a field on the first row of the table.

Steps to Resolve

The first, common step is to launch Engineering Data Manager and to create a query.

After creating a query, go to the Query Builder:

And turn on Advanced Mode.

Then write this query SELECT * FROM ProcessPhysical.DEVICE which will yield a table with all the pieces of equipment in the i-model under the OP3D schema. In this example the resulting table has only three records in order to keep it simple.

The fields Number and Plant_Area will be used for this demonstration, in order to focus exclusively on them, this query will be used SELECT Number, Plant_Area from ProcessPhysical.DEVICE

1. To combine two fields into one

In order to combine two fields into one, after the SELECT keyword, the name of both fields must be included separated by the operator ||

Using the same query as before, but adding one more field that would be the combination of Number and Plant_Area, the resulting query is:

SELECT Number, Plant_Area, Number||Plant_Area from ProcessPhysical.DEVICE

And the resulting table:

2. To change the displayed name of a field in the first row of the table

In order to accomplish this, the AS keyword needs to be used. Expanding on the example, this new query will change the first row text to read "Nº, PlantArea, Nº_PlantArea":

SELECT Number AS Nº, Plant_Area AS PlantArea, Number||Plant_Area AS Nº_PlantArea from ProcessPhysical.DEVICE

And the resulting table is: