Operators behave like they do in the calculation engine (time + integer results in time increased by number of seconds), and so on.<\/li>\n<\/ul>\nInline Dataset Syntax<\/h3>\n
Inline datasets allow you to hard-code a dataset by using the values<\/strong> keyword, instead of fetching it from an external system. In clauses which accept inline datasets, the syntax is as follows.<\/p>\n(values<\/span><\/strong> (Row1Col1,Row1Col2),<\/span> (Row2Col1,Row2Col2) ,...)<\/span><\/strong> TableName (<\/span><\/strong>Column1Name, Column2Name ,...)<\/span><\/strong><\/p>\nIn the syntax model, after \"values\":<\/p>\n
\n- First you list all your rows. The number of values for each row ((Row1Value1, Row1Value2) etc), must match the number of columns you have.<\/li>\n
- Then you give the dataset a name.<\/li>\n
- And finally you list the name of the columns.<\/li>\n<\/ul>\n
Note: Any names that contain special characters can be escaped by using square brackets e.g. [Table Name]<\/p>\n
Here is an example:<\/p>\n
Table Name: Cause<\/strong><\/p>\n\n\n\nCause<\/strong><\/td>\nId<\/b><\/td>\n<\/tr>\n\n| Planned<\/td>\n | 1<\/td>\n<\/tr>\n | \n| Unplanned<\/td>\n | 2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n For the example table above, the syntax would be as follows:<\/p>\n (values (\"Planned\", 1), (\"Unplanned\", 2)) Cause (Cause, Id)<\/p>\n
\nSupported Clauses<\/h2>\nExample Dataset<\/h3>\nIn the following examples we will use this dataset, named \"Downtime<\/strong>\":<\/p>\n\n\n\nLocation<\/strong><\/td>\n| CauseId<\/b><\/td>\n | DowntimeHours<\/b><\/td>\n<\/tr>\n\n| Michigan<\/td>\n | 1<\/td>\n | 45<\/td>\n<\/tr>\n | \n| Texas<\/td>\n | 1<\/td>\n | 712<\/td>\n<\/tr>\n | \n| Wyoming<\/td>\n | 2<\/td>\n | 130<\/td>\n<\/tr>\n | \n| Michigan<\/td>\n | 2<\/td>\n | 176<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nselect<\/h3>\nThe list of columns or expressions to return from a named dataset in IFS OI<\/a>\u00a0Server. This clause is required in all queries and supports the following features:<\/p>\n \n\n\nFeature<\/strong><\/td>\nReturns<\/strong><\/td>\nFormat<\/strong><\/td>\nExample<\/strong><\/td>\n<\/tr>\n\n| asterisk<\/td>\n | All columns from all datasets<\/td>\n | *<\/span><\/td>\n | {dq'select <\/strong>* <\/span>from<\/strong> Downtime'}<\/strong><\/td>\n<\/tr>\n\n| prefixed asterisk<\/td>\n | All columns of the specified dataset<\/td>\n | datasetName.*<\/span><\/td>\n | {dq'select<\/strong> Downtime.* <\/span>from<\/strong> Downtime'}<\/strong><\/td>\n<\/tr>\n\n| column name<\/td>\n | The specified column from any of the datasets as long as the column name is unique across all datasets<\/td>\n | columnName<\/td>\n | {dq'select <\/strong>Location from <\/strong>Downtime'}<\/strong><\/td>\n<\/tr>\n\n| prefixed column name<\/td>\n | The specified column from the specified dataset<\/td>\n | datasetName.<\/span>columnName<\/td>\n | {dq'select <\/strong>Downtime.<\/span>Location\u00a0from <\/strong>Downtime'}<\/strong><\/td>\n<\/tr>\n\n| expression<\/td>\n | The result of the expression as a column<\/td>\n | Any valid expression that can be parsed by the calculation engine<\/a> on a dataset column<\/td>\n | {dq'select <\/strong>DowntimeHours*<\/span>2<\/span> from <\/strong>Downtime'}<\/strong><\/td>\n<\/tr>\n\n| alias<\/td>\n | Specifies an alias\/name for the column<\/td>\n | columnName as <\/strong>[<\/span>alias]<\/span><\/td>\n{dq'select <\/strong>DowntimeHours as\u00a0 <\/strong>[<\/span>Downtime Hours]<\/span> from\u00a0 <\/strong>Downtime'}<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nfrom<\/h3>\n\u00a0The source dataset of the query, which can be either a named dataset in IFS OI<\/a>\u00a0Server or an Inline Dataset (see above for syntax). Supported features are:\u00a0<\/p>\n \n\n\nFeature<\/strong><\/td>\nDescription<\/strong><\/td>\nFormat<\/strong><\/td>\nExample<\/strong><\/td>\n<\/tr>\n\n| dataset reference or inline dataset<\/td>\n | Fetches the specified dataset and uses it as the source of the query<\/td>\n | from <\/strong>source<\/td>\n{dq'select<\/strong> * from <\/strong>Downtime'}<\/strong><\/td>\n<\/tr>\n\n| alias<\/td>\n | Specifies an alias\/name for the dataset<\/td>\n | from <\/strong>source as<\/strong> [<\/span>alias]<\/span><\/td>\n{dq'select<\/strong> * from <\/strong>Downtime as <\/strong>[<\/span>Monthly Downtime]<\/span>'}<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\njoin<\/h3>\n\u00a0Allows joining other datasets to the source of the query, which can be either a named dataset in IFS OI<\/a>\u00a0Server or an Inline Dataset (see above for syntax). Supported features are:<\/p>\n \n\n\nFeature<\/strong><\/td>\nDescription<\/strong><\/td>\nFormat<\/strong><\/td>\nExample<\/strong><\/td>\n<\/tr>\n\ninner join
\nleft join
\nright join
\nfull join<\/td>\n | Standard SQL syntax for multiple types of joins<\/td>\n | joinType source on\u00a0 <\/strong>joinCondition<\/td>\n{dq'select <\/strong>*<\/span> from<\/strong> Downtime inner join<\/strong> Cause on<\/strong> Downtime.<\/span>CauseId ==<\/span> Cause.<\/span>Id'}<\/strong><\/td>\n<\/tr>\n\n| cross join<\/td>\n | Standard SQL syntax for cross joins<\/td>\n | cross join<\/strong> source<\/td>\n{dq'select <\/strong>*<\/span> from\u00a0 <\/strong>Downtime cross join <\/strong>Cause'}<\/strong><\/td>\n<\/tr>\n\n| alias<\/td>\n | Specifies an alias\/name for the joined dataset. The alias is specified directly after the dataset name.<\/td>\n | dataset1Name ds1Alias
\nds1Alias.<\/span>column<\/td>\n | {dq'select <\/strong>*<\/span> from <\/strong>Downtime dt\u00a0inner join <\/strong>Cause cd on <\/strong>dt.<\/span>CauseId ==<\/span> cd.<\/span>Id'}<\/strong><\/td>\n<\/tr>\n\n| expression<\/td>\n | Specifies a condition for finding joined rows<\/td>\n | Any valid expression that can be parsed by the calculation engine<\/a> on a dataset column<\/td>\n | {dq'select <\/strong>*<\/span> from <\/strong>(<\/span>values<\/strong> (\"<\/span><\/span>one\"<\/span>),<\/span> (<\/span>\"two\"<\/span>), (<\/span>Str(<\/span>three)), (<\/span>\"four\"<\/span>))<\/span> inlineParent (<\/span>Value<\/strong>)<\/span> left join <\/strong>(<\/span>values<\/strong> (<\/span>1<\/span>), (<\/span>2<\/span>), (<\/span>3<\/span>), (<\/span>4<\/span>))<\/span> inlineChild (<\/span>ChildId)<\/span> on <\/strong>inlineParent.<\/span>Value ==<\/span> \"one\"<\/span> or <\/strong>inlineParent.Value ==<\/span> Str(<\/span>two)<\/span> or<\/strong> inlineParent.Value ==<\/span> \"three\"'}<\/strong><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nJoin Types<\/p>\n<\/h4>\n![\"\"](\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2026\/09\/join-type-1080x161.png\") <\/a><\/p>\n
\n\n\n| Left Join<\/td>\n | Returns all records from the left dataset and matching records from the right dataset.<\/td>\n<\/tr>\n | \n| Right Join<\/td>\n | Returns all records from the right dataset and matching records from the left dataset.<\/td>\n<\/tr>\n | \n| Inner Join<\/td>\n | Returns only records with matching values in both datasets.<\/td>\n<\/tr>\n | \n| Full Join<\/td>\n | Returns all records from both datasets, including matching and non-matching records.<\/td>\n<\/tr>\n | \n| Feature<\/td>\n | Returns all rows from both datasets without using a join condition.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nwhere<\/h3>\n\u00a0Filters the rows of the query. Supported features are:<\/p>\n \n\n\nFeature<\/strong><\/td>\nDescription<\/strong><\/td>\nFormat<\/strong><\/td>\nExample<\/strong><\/td>\n<\/tr>\n\n| expression<\/td>\n | The expression\/condition to apply to the rows<\/td>\n | Any valid expression that can be parsed by the calculation engine<\/a> on a dataset column<\/td>\n | {dq'select <\/strong>*<\/span> from\u00a0 <\/strong>Downtime where <\/strong>(<\/span>DowntimeHours ><\/span> 4<\/span> or<\/strong> Location ==<\/span> \"Texas\"<\/span>)<\/span>'}<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\norder by<\/h3>\nSorts the rows of the query. Ordering can be ascending (default) or descending (desc). Supported features are:<\/p>\n \n\n\nFeature<\/strong><\/td>\n| Description<\/strong><\/td>\n | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |