SQL queries

A data source defined in CADMATIC eShare can be seen as a question such as “If I have an object with tag ‘sys’ having value ‘12345’, what is the delivery status of this object?”. An SQL query is a formal way of representing such question. The exact format of the query might depend on database type, but simple SQL queries are usually similar in different databases.

A basic SQL query uses the following structure:

SELECT ColumnName1, ColumnName2
FROM TableName
WHERE ObjectId = @Key

When this kind of query is executed in CADMATIC eShare, @Key is replaced with the value of the object tag that triggered the query. The list of column names defines the columns in the result.

Defining display attributes

Once you have defined the SQL query, enter a value in the Test object ID field and click Execute Query. This populates the Displayed Attributes table, and you can now fill in the Categories and Display Names that are used when showing the resulting attributes to the user. When displaying attributes, they are grouped using the categories. If no display names are defined, the attributes are shown with their column names.

The test id for the query needs to be valid, meaning that it needs to have the correct data type. If you enter a value that does not exist in the database, the Displayed Attributes table will be updated but the Test Results will be empty. If you enter a value that does exist, the Test Results shows the data returned by the query.

Example SQL queries

SQL queries can be written on a single line, but here they are displayed on multiple lines to enhance readability.

Values from the same table as the key

SELECT DeliveryStatus, OrderDate
FROM ErpData
WHERE id = @Key

This query returns the columns ‘DeliveryStatus’ and ‘OrderDate’ from the database table ‘ErpData’, from the row where the value of the ‘id’ column is the value of the tag served.

Values from different tables

SELECT DeliveryData.DeliveryDate, OrderData.Order
FROM DeliveryData
INNER JOIN OrderData
ON DeliveryData.OrderId = OrderData.Id
WHERE id = @Key

This query assumes that you have the tables ‘DeliveryData’ and ‘OrderData’ in your database. ‘DeliveryData’ table has columns ‘id’, ‘OrderId’, and ‘DeliveryDate’. ‘OrderData’ table has columns ‘Order’ and ‘Id’.

The query finds the row in ‘DeliveryData’ in which the ‘id’ column has the value of the tag. It then finds the row from the ‘OrderData’ table where the ‘Id’ column has the same value as the ‘OrderId’ column in the row in ‘DeliveryData’. The displayed values will be selected from the ‘DeliveryDate’ and ‘Order’ columns of the two tables.

Values from different tables with identical column names

SELECT DeliveryData.OfficialDate as deliveryDate, OrderData.OfficialDate as orderDate
FROM DeliveryData
INNER JOIN OrderData
ON DeliveryData.OrderId = OrderData.Id
WHERE id = @Key

This query assumes that you have the tables ‘DeliveryData’ and ‘OrderData’ in your database. ‘DeliveryData’ table has columns ‘id’, ‘OrderId’, and ‘OfficialDate’. ‘OrderData’ table has columns ‘OfficialDate’ and ‘Id’.

The query finds the row in ‘DeliveryData’ in which the ‘id’ column has the value of the tag. It then finds the row from the ‘OrderData’ table where the column ‘Id’ has the same value as the ‘OrderId’ column in the row in ‘DeliveryData’. The result is that the values from the ‘OfficialDate’ column of both tables will be selected. The Displayed Attributes table of CADMATIC eShare will show these as ‘deliveryDate’ and ‘orderDate’.

Tag calue divided to values of multiple columns

SELECT Standard
FROM Pipes
WHERE Area = SUBSTRING(@Key, 1,2) AND Line = SUBSTRING(@Key,6,5)

This query compares parts of the value of the tag with two different columns in the database. It assumes that the value of the tag has 10 characters, and that the database has the table ‘Pipes’ with the columns ‘Standard’, ‘Area’, and ‘Line’. The value of the pipeline id is contained in the Area and Line columns.

The query compares the first five characters of the tag value with the ‘Company’ column, and the last five characters of the tag value with the ‘Location’ column. The return value is the value of the ‘Email’ column in such a row.

Please note that this example is for Microsoft SQL Server. Other database types might have different methods for string handling.

More complex SQL queries

Depending on the database or system that the information comes from, formulating the SQL queries might not always be straightforward. In this section we cover some cases that could be encountered when creating categorization for external data sources. Basically any case where it's possible to write a SELECT clause that produces results with the category and the required Tag values can be used as a categorization data source.

The examples are presented using the SQL syntax of Microsoft SQL Server. In some of the more complex cases, the format of the SQL statements might depend on the database system.

Required data is split over multiple tables

Sometimes the required information cannot be found from a single database table, and the query needs to refer to multiple tables. For example, valve positions might be mapped to internal object identifiers in IdTable, while the actual category values are defined in StatusTable using the internal IDs instead of the original valve positions. This could be handled with the following query.

Tags: vpo

Query: SELECT StatusTable.Status, IdTable.ValvePosition
FROM IdTable, StatusTable
WHERE IdTable.InternalId = StatusTable.InternalId

Tag value is split into multiple columns

Sometimes database column values might not directly match Tag values. For example, the System (sys) tag in the model can consist of name and ID separated by an underscore (‘Maintenance_001’), but in the database the name and ID might be stored in separate columns. This could be handled by combining the column values in the query, as shown below.

Tags: sys

Query: SELECT Status, (SystemName + '_' + SystemId) AS CombinedName
FROM SystemTable

Now the values of sys are matched against the returned CombinedName column.

Multiple result rows for each object

Sometimes a database table might have multiple entries for a single object, with different values. For example, a table might contain the results of valve safety inspections, including all previous inspections. For categorization, you would only want to display the results of the latest inspection, to show the current status of an item.

Tags: vpo

Query: SELECT Status, ValvePosition
FROM ValveTable AS T
WHERE InspectionDate =
(SELECT MAX(InspectionDate)
FROM ValveTable
WHERE ValvePosition = T.ValvePosition)

Database values are not directly mapped to categories

Sometimes categorization needs to be using value ranges, instead of the individual values of a single database column. For example, you might want to categorize pipelines based on their length, but not have a separate category for every possible length. In this case, the query could specify the categories for level ranges and return that as the first column of the results, as described below.

Tags: pli

Query: SELECT CASE WHEN PipeLength BETWEEN 0 AND 9 THEN 'Short' WHEN PipeLength BETWEEN 10 AND 19 THEN 'Medium' ELSE 'Long' END AS LengthRange, Pipeline FROM PipeTable

This query adds the pipelines with length between 0 and 9 to category “Short”, pipelines with length between 10 and 19 to category “Medium”, and the rest into category “Long”.

Combining categories

Category definitions can use the same display name for several categories, to combine them into a single category. This can specified manually or by using a query that returns the same value for several items, in a manner similar to the previous example. For example, you might want to combine the status column values ‘Damaged’ and ‘Disconnected’ into ‘Needs Maintenance’, as described below.

Tags: ipo

Query: SELECT CASE WHEN Status = ‘Damaged’ THEN 'Needs Maintenance'
WHEN Status = ‘Disconnected’ THEN 'Needs Maintenance'
ELSE Status END AS CategoryValue, InstrumentPosition
FROM InstrumentTable

Using multiple queries

When one query is not enough to provide all the required categorization data, you can add multiple queries to a single categorization data source. For instance, you can use this when categorization information is stored in different ways or in different tables.

For example, categorization might be based on the status of equipment, but the database might not have the status as a separate column anywhere, but instead have a table with the list of all the damaged components, another for components that have been ordered but not yet delivered etc. You can solve this case by making a separate query to each of the tables.

Query 1:

Tags: ipo

Query: SELECT 'Damaged' AS Status, InstrumentPosition FROM DamagedInstrumentsTable

Query 2:

Tags: ipo

Query: SELECT 'Ordered' AS Status, InstrumentPosition FROM InstrumentOrdersTable

This configuration would cause both queries to be performed with instruments found in the DamagedInstrumentsTable to be placed in the Damaged category and instruments from InstrumentOrdersTable to be placed in the Ordered category.

When using multiple queries, it is also possible to have them based on different tags, or even on Compound Tags with a different number of Tags (within a single query, Compound Tags must still consist of the same number of Tags as always). This can be useful when the same categorization needs to be used for different kinds of items, for example for Valves (using Valve Position Tag vpo) and for Spools (using a Combination of Isometric Drawing Number Idn and Spool Number spn). For example:

Query 1:

Tags: vpo

Query: SELECT Status, ValvePosition FROM ValveStatusTable

Query 2:

Tags: Idn, spn

Query: SELECT Status, DrawingNumber, SpoolNumber FROM SpoolStatusTable