Documentation Portal

This article shows some concrete examples which can help you set up your ODBC data source. More information can be found in this article.

The following examples assume that separate queries (values-STRING.sql) weren’t needed for pulling string tags.

If your database table contains different description, units and types for a single tag name, you can use the SQL function FIRST_VALUE() to get the first value in an ordered partition of a result set. The following query will return the first description, unit and type it encounters for every distinct “tagname”.

SELECT DISTINCT  
        tagname,  
        FIRST_VALUE(description) OVER (PARTITION BY tagname ORDER BY tagname) as description,  
        FIRST_VALUE(units) OVER (PARTITION BY tagname ORDER BY tagname) as units,  
        FIRST_VALUE(type) OVER (PARTITION BY tagname ORDER BY tagname) as type,  
FROM Table_1  

In case where the values.sql query pulls data and there is more than one value for the same timestamp, you should only pick one (first value) or TrendMiner will show both values for the same timestamp. The following query will return the first “value” encountered for a given tag and date range.

SELECT DISTINCT  
        FIRST_VALUE(value) OVER (PARTITION BY timestamp ORDER BY value desc) as value,  timestamp as ts  
FROM Table_1  
WHERE tagname = ? AND timestamp IS BETWEEN ? AND ? ORDER BY timestamp ASC  

If the values table contains entries (rows) with an empty (null) value, the generic values.sql query will look as follows

SELECT  
        value,  
        timestamp as ts  
FROM Table_1   
WHERE tagname = ? AND timestamp IS BETWEEN ? AND ? AND value IS NOT NULL ORDER BY timesamp ASC  

Use a UNION statement to combine the output of two different tables (Table_1 and Table_2) into a single list.sql query.

SELECT  
        tagname,  
        description,  
        type,  
        units
FROM Table_1  
UNION  
SELECT  
        tagname,  
        description,  
        type,  
        units  
FROM Table_2  

Depending on the table schema, you might want to concatenate (combine) multiple columns to generate a unique tagname as shown below. You might also want to do the same for the description as well.

SELECT DISTINCT  
        (CAST(COMPONENT_NAME as varchar) + CAST(TANK_NUMBER as varchar)) as tagname,  
        description,  
        type,  
        units  
FROM Table_1  

In this case, assuming the values and timestamps originate from the same table, the values.sql query might look as follows

SELECT  
        value2,  
        FORMAT(value2, 'N', 'en-us') as value2_US_Format,  
        FORMAT(value2, 'N', 'de-de') as value2_DE_Format  
FROM Table_1  

You might need to combine (join) multiple tables to generate a query output with all the necessary information for the list.sql file. In the query shown below, “TagName” and “Description” are both part of the table “_Tag”. However, the type and units are part of the “TagType” and “EngineeringUnit” tables respectively, pulled using the corresponding keys.

SELECT DISTINCT 
        TagName as tagname, 
        Description as description, 
        Unit as units, 
        TagTypeName as type 
FROM _Tag 
LEFT JOIN TagType ON _Tag.TagType=TagType.TagTypeKey 
LEFT JOIN EngineeringUnit ON _Tag.EUKey=EngineeringUnit.EUKey