The query-expr is an SQL SELECT query that returns a table.
Format
SELECT [ TOP ( expression ) [ PERCENT ] ] [ WITH TEMPLATES ] [ ALL | DISTINCT ] { * | select-element [ , select-element ]* )
FROM join-expr
[ WHERE bool-condition ]
[ GROUP BY column-ref [ , column-ref ]* ]
[ HAVING bool-condition
]
Remarks
The SELECT clause returns rows from one or more tables specified by the join-expr clause.
You have to include a WHERE clause when querying historic tables, to restrict the amount of data that is being queried (see Requirements when Querying Historic Tables).
The columns returned in the result-set are specified using either an asterisk (returning all columns), or a column separated list of select-element.
The optional TOP clause restricts the result-set to the specified number of rows. The PERCENT option, limits the rows returned to a percentage of the total number of rows. Without the PERCENT option, TOP returns a result-set containing no more than the specified number of rows.
The optional WITH TEMPLATES clause includes template objects in the result set.
The WITH TEMPLATES clause applies only to the first table in a JOIN clause.
You can use the de-reference operator, which uses the -> syntax, to refer to other aggregate tables in a SELECT query that includes the WITH TEMPLATES clause. This enables the result set to return results for the table(s) that are in the FROM clause and any of their aggregate tables that are referenced using the -> syntax (see Example 4 below). Unexpected results might be returned if further tables are referenced in conditional JOIN clauses, if those tables do not include rows that are defined within the Group Templates.
The keywords ALL and DISTINCT control whether duplicate rows are returned in the result-set. The ALL option allows duplicated rows. The DISTINCT option removes duplicated rows. If neither ALL nor DISTINCT are specified, ALL is assumed.
The optional WHERE clause filters the result set to only include rows for which the associated condition is true.
The optional GROUP BY clause is intended to be used in conjunction with aggregate functions (see expr-primary-sumfunc ). The GROUP BY clauses specifies a list of columns to create result set groups. All other columns not specified by the GROUP BY clause must be included via an expression including an aggregate function.
The optional HAVING clause further filters the result set to only include groups for which the associated condition is true.
Example 1: Return the 10 most recently updated points:
SELECT TOP( 10 ) Id,FullName,CurrentTime
FROM CDBPOINT
ORDER BY CurrentTime
Example 2: Return a list of database groups with points together with the number of points in each group:
SELECT ParentGroupName,Count(*)
FROM CDBPOINT
GROUP BY ParentGroupName
Example 3: Return a list of database groups with more than 5 points in a group, together with the number of points in each group:
SELECT ParentGroupName,Count(*)
FROM CDBPOINT
GROUP BY ParentGroupName
HAVING Count(*) > 5
Example 4: Return a list of data about the analog points that are in Group Instances and Group Templates, including data that is extracted from an aggregate of the first table in the SELECT query:
SELECT WITH TEMPLATES CDNP3ANALOGOUT.FULLNAME, CDNP3ANALOGOUT.TYPENAME, CDNP3ANALOGOUT.ID, CDNP3ANALOGOUT.CONTROL->CONTROLCONFIRM
FROM CDNP3ANALOGOUT
The de-reference operator, which uses the -> syntax, is used to reference the Control aggregate of the CDNP3AnalogOut table (which itself comprises the CDNP3AnalogCtrl table). The -> syntax is used in this query as the WITH TEMPLATES clause only applies to the first table that is referenced in the query (due to it being the only table that is listed in the FROM clause).
Example 5: Return a list of data about the analog points that are in Group Instances and Group Templates, without using the de-reference operator:
SELECT WITH TEMPLATES CDNP3ANALOGOUT.FULLNAME, CDNP3ANALOGOUT.TYPENAME, CDNP3ANALOGOUT.ID, CDNP3ANALOGCTRL.CONTROLCONFIRM
FROM CDNP3ANALOGOUT, CDNP3ANALOGCTRL
WHERE CDNP3ANALOGOUT.ID = CDNP3ANALOGCTRL.ID
The above query returns the results of both the CDNP3AnalogOut table and its Control aggregate (which comprises the CDNP3AnalogCtrl table) without using the de-reference operator. This is because all of the tables that are listed in the WITH TEMPLATES clause are also included in the FROM and WHERE clauses.
Example 6: Return a list of data about the analog points that are in Group Instances, without returning data from the Control aggregate of analog points that are in Group Templates:
SELECT WITH TEMPLATES CDNP3ANALOGOUT.FULLNAME, CDNP3ANALOGOUT.TYPENAME, CDNP3ANALOGOUT.ID, CDNP3ANALOGCTRL.CONTROLCONFIRM
FROM CDNP3ANALOGOUT LEFT JOIN CDNP3ANALOGCTRL ON CDNP3ANALOGOUT.ID = CDNP3ANALOGCTRL.ID
Like Example 5, the above query returns the results of both the CDNP3AnalogOut table and its Control aggregate (which comprises the CDNP3AnalogCtrl table) without using the de-reference operator, due to both tables being referenced in the FROM clause. Additionally, the query excludes data from the Control aggregate of analog points that are in Group Templates due to the conditional JOIN being used (which includes the LEFT and ON syntax).
Further Information
Template objects: see Group Templates and Group Instances in the Geo SCADA Expert Guide to Core Configuration.