{"id":3775,"date":"2017-06-26T18:46:20","date_gmt":"2017-06-26T10:46:20","guid":{"rendered":"http:\/\/localhost\/help\/?page_id=3775"},"modified":"2024-11-22T14:48:17","modified_gmt":"2024-11-22T06:48:17","slug":"how-to-write-a-dataset-query","status":"publish","type":"page","link":"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/connecting-your-data\/creating-a-dataset-datasource\/how-to-write-a-dataset-query\/","title":{"rendered":"How to Write a Dataset Query"},"content":{"rendered":"

<\/p>\n

Datasets are backed by SQL queries that return data when executed against their associated datasource<\/a>. IFS OI Server provides a number of keywords that allow a degree of customisation of such SQL queries, such as attaching user-supplied parameters to the query at execution time. These keywords are pre-processed before the SQL query is executed, so the one dataset definition can be reused for variations on the query (such as in a WHERE clause) rather than having to define multiple, similar dataset queries.<\/p>\n

When the query is pre-processed, these keywords are replaced in the query as described in the table below. When used, keywords that accept parameters will appear as options of the dataset in IFS OI Explorer.<\/p>\n

Datasets that have parameters attached corresponding to entity names can also make use of the Entity Native Name Mapping feature, allowing datasources that use different names for identifying the same asset to be queried transparently using the entity names defined in IFS OI Server.<\/p>\n

The following keywords are associated with datasets:<\/p>\n\n\n\n\n\n\n\n
Keyword<\/strong><\/td>\nUsage<\/b><\/span><\/td>\nReplacement Example
\n(SQL Server)<\/b><\/span><\/td>\n		Description<\/strong><\/td>\n<\/tr>\n
Param<\/strong><\/td>\n\n

PARAM(parameter_name,data_type)<\/p>\n

Example:
\nPARAM(prodStartTime,datetime)<\/p>\n<\/td>\n

\n

@parameter_name<\/p>\n

@prodStartTime<\/p>\n<\/td>\n

\n

A single value that is passed to the query by IFS OI Explorer. This appears in IFS OI Explorer as one of the parameters that the user must supply when configuring the dataset.<\/p>\n

    \n
  • Parameter_name<\/strong>: The name of the parameter which will appear in IFS OI Explorer. This keyword is case sensitive and does not accept spaces.<\/li>\n
  • Data_type<\/strong>: The type of data expected by the parameter. Valid values are: string, integer, boolean, datetime, entity.<\/li>\n<\/ul>\n

    When using the Entity data type, this keyword will check its\u00a0supplied parameters to see if it uses an\u00a0entity name with a defined native name for the data source. If such a mapping exists, the native name will be applied to the query instead.<\/p>\n<\/td>\n<\/tr>\n

Params<\/strong><\/td>\n\n

PARAMS(parameter_name_csv,data_type)<\/p>\n

Example:
\nPARAMS(names,entity)<\/p>\n<\/td>\n

\n

@parameter_name1, @parameter_name2<\/p>\n

@names1, @names2, \u2026 @namesX<\/p>\n<\/td>\n

\n

A comma-separated list of values that is passed to the query by IFS OI Explorer. This appears in IFS OI Explorer as one of the parameters that the user must supply when configuring the dataset.<\/p>\n

    \n
  • Parameter_name_csv<\/strong>: The name of the parameter which will appear in IFS OI Explorer. This parameter accepts a comma-separated list of names and is mostly used with the entity<\/em> data type. This keyword is case sensitive and does not accept spaces.<\/li>\n
  • Data_type<\/strong>: The type of data expected by the parameter. Valid values are: string, integer, boolean, datetime, entity.<\/li>\n<\/ul>\n

    When using the Entity data type, this keyword will check its\u00a0supplied parameters to see if they are entity names with a defined native name for the data source. If such a mapping exists, the native name will be applied to the query instead.<\/p>\n<\/td>\n<\/tr>\n

Sortby<\/strong><\/td>\n\n

SORTBY(colname1 [direction][,colnameX [direction]\u2026])<\/p>\n

Example:
\nSORTBY(PRODUCTION_ENTITY_NAME)<\/p>\n

\u00a0
\nSORTBY(PRODUCTION_ENTITY_NAME DESC, PRODUCTION_DATE)<\/p>\n<\/td>\n

\n

 <\/p>\n

 <\/p>\n

ORDER BY PRODUCTION_ENTITY_NAME<\/p>\n

ORDER BY PRODUCTION_ENTITY_NAME DESC, PRODUCTION_DATE<\/p>\n<\/td>\n

\n

Specifies the name of the columns that determines the ordering of the query results. This does not appear in IFS OI Explorer when configuring the dataset.<\/p>\n

    \n
  • col_name<\/strong>: The name of the column by which to sort. To sort by multiple columns, use a comma\u00a0to separate the column names. If you specify more than 1 column, the order of sorting is determined by the order in which you specify the columns.<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n
Entityname<\/strong><\/td>\n\n

ENTITYNAME(column_expression[,alias[,sort]])<\/p>\n

Example:
\nENTITYNAME(UPPER(name))<\/p>\n

ENTITYNAME(UPPER(name),Uppercase)<\/p>\n

ENTITYNAME(UPPER(name),Uppercase,asc)<\/p>\n<\/td>\n

\n

 <\/p>\n

\u00a0
\nUPPER(name)<\/p>\n

UPPER(name) AS Uppercase<\/p>\n

UPPER(name) AS Uppercase<\/p>\n

 <\/p>\n<\/td>\n

\n

Allows you to map an entity in IFS OI Server to a specific unique identifier in a source system.<\/p>\n