How to Write a Dataset Query

Datasets are backed by SQL queries that return data when executed against their associated datasource. P2 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.

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 P2 Explorer.

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 P2 Server.

The following keywords are associated with datasets:

Keyword	Usage	Replacement Example (SQL Server)	Description
Param	PARAM(parameter_name,data_type) Example: PARAM(prodStartTime,datetime)	@parameter_name @prodStartTime	A single value that is passed to the query by P2 Explorer. This appears in P2 Explorer as one of the parameters that the user must supply when configuring the dataset. Parameter_name: The name of the parameter which will appear in P2 Explorer. This keyword is case sensitive and does not accept spaces. Data_type: The type of data expected by the parameter. Valid values are: string, integer, boolean, datetime, entity. When using the Entity data type, this keyword will check its supplied parameters to see if it uses an entity 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.
Params	PARAMS(parameter_name_csv,data_type) Example: PARAMS(names,entity)	@parameter_name1, @parameter_name2 @names1, @names2, … @namesX	A comma-separated list of values that is passed to the query by P2 Explorer. This appears in P2 Explorer as one of the parameters that the user must supply when configuring the dataset. Parameter_name_csv: The name of the parameter which will appear in P2 Explorer. This parameter accepts a comma-separated list of names and is mostly used with the entity data type. This keyword is case sensitive and does not accept spaces. Data_type: The type of data expected by the parameter. Valid values are: string, integer, boolean, datetime, entity. When using the Entity data type, this keyword will check its supplied 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.
Sortby	SORTBY(colname1 [direction][,colnameX [direction]…]) Example: SORTBY(PRODUCTION_ENTITY_NAME)   SORTBY(PRODUCTION_ENTITY_NAME DESC, PRODUCTION_DATE)	ORDER BY PRODUCTION_ENTITY_NAME ORDER BY PRODUCTION_ENTITY_NAME DESC, PRODUCTION_DATE	Specifies the name of the columns that determines the ordering of the query results. This does not appear in P2 Explorer when configuring the dataset. col_name: The name of the column by which to sort. To sort by multiple columns, use a comma to 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.
Entityname	ENTITYNAME(column_expression[,alias[,sort]]) Example: ENTITYNAME(UPPER(name)) ENTITYNAME(UPPER(name),Uppercase) ENTITYNAME(UPPER(name),Uppercase,asc)	UPPER(name) UPPER(name) AS Uppercase UPPER(name) AS Uppercase	Allows you to map an entity in P2 Server to a specific unique identifier in a source system. Column_expression: This is what would normally go in the query to return the native name (eg. “SELECT name_column FROM…” becomes “SELECT ENTITYNAME(name_column) FROM…”). This can be a column name or a function expression. Alias: (optional) An alternative name/header to use for the column, which will be turned into an AS clause. This needs to be specified as a parameter to the keyword rather than AS in the column expression so the adaptor can figure out which column to do remapping on. If left empty, the column name will be unchanged. Sort: (optional) “asc” or “desc” for specifying the sort direction after replacement (but see limitations below).

 

Example Simple Queries

Choke data for oil wells for the selected timestamp and entity. The Explorer user must supply an entity name and a datetime when they configure the dataset. 

select top 1 Node,Choke,GLChoke,MaxChoke 
from OilWells 
where Node = PARAM(well,entity) and TimeStamp <= PARAM(time,datetime) 
order by TimeStamp desc

Get daily or monthly production values for a trend, for the selected completion, within the selected period. The Explorer user must supply a list of completions and the reading type when they configure the dataset.

select cost_center as COMPLETION, OIL, GAS, WATER, prod_date as PROD_DATE 
from etp_production_values 
where cost_center in (PARAMS(completionNames,entity)) 
and reading_type = PARAM(readingType,string) 
and prod_date between '1-Jan-2014' 
and '1-Mar-2015'

 

Example SELECT Queries using Entityname()

1. Accepts entities, performs the query using their native names then substitutes the original entity names in the result set. Example:

select ENTITYNAME(cost_center_name) 
from P2_PRODUCTION_METRICS 
where cost_center_name 
in (PARAMS(entities,entity))

 

2. Accepts entities, performs the query using their native names then substitutes the original entity names in the result set. 
However, the returned column will be titled “Centre” rather than “cost_center_name”. Example:

select ENTITYNAME(cost_center_name,Centre) 
from P2_PRODUCTION_METRICS 
where cost_center_name 
in (PARAMS(entities,entity))

 

3. Accepts entities, performs the query using their native names then substitutes the original entity names in the result set.
However, the results will be sorted in reverse alphabetical order and the column will have its original title (“cost_center_name”). Example:

select ENTITYNAME(cost_center_name,,desc) 
from P2_PRODUCTION_METRICS 
where cost_center_name 
in (PARAMS(entities,entity))

 

4. Accepts entities, performs the query using their native names then substitutes the original entity names in the result set. 
However, the returned column will be titled “Centre” rather than “cost_center_name” and the rows will be sorted in alphabetical order. Example:

select ENTITYNAME(cost_center_name,Centre,asc) 
from P2_PRODUCTION_METRICS 
where cost_center_name 
in (PARAMS(entities,entity))

 

Limitations of Entity Native Name Mapping

General

• The native name specified for a mapping is case sensitive (while the entity name is not, as per the rest of the system). The query must generate results in the same casing as defined in the native name table for the reverse mapping to work.

Sorting

• Sorting is performed in memory after the result set is returned and the names remapped.
• If a sort is specified in the query (other than on the ENTITYNAME() keyword) the ordering will be based on the pre-remapping values of the column. As a result, ENTITYNAME() sorting is likely to be incompatible with queries written to support paging, unless such queries are ordered by a different column to the ENTITYNAME().
• If no sort is specified, the rows returned will have the same order they did in the pre-remapping result set returned to the adaptor.
• If no alias is specified, reverse mapping should attempt to find a column with the same name as the text of column_expression.

Alias

• The column_expression or alias must appear as a column heading on the final result set for remapping to work.

Entity Names

• Only one ENTITYNAME() column in the query can have a sort specified.
• Mapping multiple entity names to the same native name may not work.