Dataset Query Language (DQL)

DQL or Dataset Query Language is using a mixture of SQL and SER syntax and allows executing queries on datasets (matrices) just like on a table in a database.

Note: All processing is done in memory, so memory consumption can become an issue.

Syntax Format

All DQL queries start with {dq'select and end with '} 

Example: {dq'select * from source'}

Expression Syntax

Where supported, expressions in all clauses follow the syntax of the Calculation Engine instead of SQL. This means that, for example:

• The == operator has to be used for equality checks instead of = (like in SQL).
• Strings can use single quote, double quote or the Str() format.
• Parameters are accessible (param[xyz]).
• Durations and times can be declared with the usual syntax ({du'xyz'} and {utc'xyz'} syntax).
• Operators behave like they do in the calculation engine (time + integer results in time increased by number of seconds), and so on.

Inline Dataset Syntax

Inline datasets allow you to hard-code a dataset by using the values keyword, instead of fetching it from an external system. In clauses which accept inline datasets, the syntax is as follows.

(values (Row1Col1,Row1Col2), (Row2Col1,Row2Col2) ,...) TableName (Column1Name, Column2Name ,...)

In the syntax model, after "values":

• First you list all your rows. The number of values for each row ((Row1Value1, Row1Value2) etc), must match the number of columns you have.
• Then you give the dataset a name.
• And finally you list the name of the columns.

Note: Any names that contain special characters can be escaped by using square brackets e.g. [Table Name]

Here is an example:

Table Name: Cause

Cause	Id
Planned	1
Unplanned	2

For the example table above, the syntax would be as follows:

(values ("Planned", 1), ("Unplanned", 2)) Cause (Cause, Id)

---

Supported Clauses

Example Dataset

In the following examples we will use this dataset, named "Downtime":

Location	CauseId	DowntimeHours
Michigan	1	45
Texas	1	712
Wyoming	2	130
Michigan	2	176

select

The list of columns or expressions to return from a named dataset in IFS OI Server. This clause is required in all queries and supports the following features:

Feature	Returns	Format	Example
asterisk	All columns from all datasets	*	{dq'select * from Downtime'}
prefixed asterisk	All columns of the specified dataset	datasetName.*	{dq'select Downtime.* from Downtime'}
column name	The specified column from any of the datasets as long as the column name is unique across all datasets	columnName	{dq'select Location from Downtime'}
prefixed column name	The specified column from the specified dataset	datasetName.columnName	{dq'select Downtime.Location from Downtime'}
expression	The result of the expression as a column	Any valid expression that can be parsed by the calculation engine on a dataset column	{dq'select DowntimeHours*2 from Downtime'}
alias	Specifies an alias/name for the column	columnName as [alias]	{dq'select DowntimeHours as  [Downtime Hours] from  Downtime'}

from

 The source dataset of the query, which can be either a named dataset in IFS OI Server or an Inline Dataset (see above for syntax). Supported features are: 

Feature	Description	Format	Example
dataset reference or inline dataset	Fetches the specified dataset and uses it as the source of the query	from source	{dq'select * from Downtime'}
alias	Specifies an alias/name for the dataset	from source as [alias]	{dq'select * from Downtime as [Monthly Downtime]'}

join

 Allows joining other datasets to the source of the query, which can be either a named dataset in IFS OI Server or an Inline Dataset (see above for syntax). Supported features are:

Feature	Description	Format	Example
inner join left join right join full join	Standard SQL syntax for multiple types of joins	joinType source on  joinCondition	{dq'select * from Downtime inner join Cause on Downtime.CauseId == Cause.Id'}
cross join	Standard SQL syntax for cross joins	cross join source	{dq'select * from  Downtime cross join Cause'}
alias	Specifies an alias/name for the joined dataset. The alias is specified directly after the dataset name.	dataset1Name ds1Alias ds1Alias.column	{dq'select * from Downtime dt inner join Cause cd on dt.CauseId == cd.Id'}
expression	Specifies a condition for finding joined rows	Any valid expression that can be parsed by the calculation engine on a dataset column	{dq'select * from (values ("one"), ("two"), (Str(three)), ("four")) inlineParent (Value) left join (values (1), (2), (3), (4)) inlineChild (ChildId) on inlineParent.Value == "one" or inlineParent.Value == Str(two) or inlineParent.Value == "three"'}

where

 Filters the rows of the query. Supported features are:

Feature	Description	Format	Example
expression	The expression/condition to apply to the rows	Any valid expression that can be parsed by the calculation engine on a dataset column	{dq'select * from  Downtime where (DowntimeHours > 4 or Location == "Texas")'}

order by

Sorts the rows of the query. Ordering can be ascending (default) or descending (desc). Supported features are:

Feature	Description	Format	Example
By column	Sorts the results according to the specified column name	order by columnName	{dq'select * from Downtime order by DowntimeHours'} {dq'select * from Downtime order by DowntimeHours desc'}
By expression	Sorts the results according to the specified expression	Any valid expression that can be parsed by the calculation engine on a dataset column	{dq'select * from Downtime  order by DowntimeHours > 25.5'}
By list of items	Comma separated list of column names and/or expressions	order by columnName, ...	{dq'select * from Downtime  order by Location desc, DowntimeHours'}

Examples

Query: {dq'select * from Downtime'}
Result: Return all columns and rows from the Downtime dataset.

Query: {dq'select * from [Downtime] as [dt]'}
Result:  Same as the previous but with an alias and with identifiers escaped.

Query: {dq'select * from (values (1, "One"), (2, "Two"), (3, "Three")) [source] (Id, [Name])'}
Result: Return all rows and columns from an inline dataset which has 2 columns (Id and Name) and 3 rows.

Query: {dq'select * from Downtime cross join Cause'}
Result: Cross join of 2 datasets.

Query: {dq'select * from Downtime cross join (values (1, "Inline 1"), (2, "Inline 2")) inlineDataset(Id, Location)'}
Result: Cross join between a fetched dataset and an inline dataset.

Query: {dq'select * from Downtime dt left join (values (1, "Inline 1"), (param[dtHrs] - 6, "Inline 2")) [inlineDataset] ([Id], [Location]) on dt.CauseId == inlineDataset.Id'}
Result: Left join between a fetched dataset and an inline dataset.

Query: {dq'select * from Downtime where (DowntimeHours > 4 or Location == "Texas")'}
Result: Filtering rows by multiple conditions.

Query: {dq'select true, {utc''2023-01-30 14:38:24''}, 123.456, {du''07:08''}, 901, ''single quote string'' [Single Quote String], "double quote string" as [Double Quote String], Str(wrapped string) WrappedString'}
Result: Testing some specific syntax (with no from clause).