{"id":15258,"date":"2017-06-26T08:31:01","date_gmt":"2017-06-26T00:31:01","guid":{"rendered":"http:\/\/localhost\/help\/?page_id=15258"},"modified":"2026-09-29T15:44:14","modified_gmt":"2026-09-29T07:44:14","slug":"writing-queries-for-the-relational-adaptor","status":"publish","type":"page","link":"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/primers\/datasources\/writing-queries-for-the-relational-adaptor\/","title":{"rendered":"Writing Queries for the Relational Adaptor"},"content":{"rendered":"\n<p class=\"intro-text\">There are 3 important\u00a0types of parameters for the Relational Adaptor.<\/p>\n<table class=\"table-toc\">\n<tbody>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\"><strong>Type of parameter<\/strong><\/td>\n<td><strong>Parameters of this type<\/strong><\/td>\n<td><strong>Explanation<\/strong><\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">Connection Parameters<\/td>\n<td width=\"210\">\n<ul>\n<li>Connection Type<\/li>\n<li>Server<\/li>\n<li>Database*<\/li>\n<li>Use Trusted Connection<\/li>\n<li>User ID<\/li>\n<li>Password<\/li>\n<\/ul>\n<\/td>\n<td>\n<p>Connection parameters are used to connect to a database.<\/p>\n<p>*Database is only required is the Connection Type is <em>Microsoft SQL Server.<\/em>\u00a0<\/p>\n<p>For Oracle, you may need to set the server to the relevant\u00a0TNS of your\u00a0datasource.\u00a0E.g. <br \/>\n(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1522))(CONNECT_DATA=(SID = t721)))<\/p>\n<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">Query Parameters<\/td>\n<td>\n<ul>\n<li>Available Tags Query<\/li>\n<li>Single Point Raw Query<\/li>\n<li>Historical Raw Query<\/li>\n<\/ul>\n<\/td>\n<td>The query parameters are used to\u00a0fetch tags and tag values. These parameters are SQL queries that are\u00a0usually\u00a0expressed\u00a0with keywords specific to the Relational Adaptor, to make your queries more easily understood.<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">Column Parameters<\/td>\n<td>Required:<\/p>\n<ul>\n<li>Tag Name Column<\/li>\n<li>Data Timestamp\u00a0Column<\/li>\n<li>Data Value Column (used only for Narrow type)<\/li>\n<li>Query Type<\/li>\n<li>Available Tags Query<\/li>\n<li>Single Point Raw Query<\/li>\n<li>Historical Raw Query<\/li>\n<\/ul>\n<p>Optional:<\/p>\n<ul>\n<li>Tag Description Column<\/li>\n<li>Tag Unit Column<\/li>\n<li>Tag Maximum Value Column<\/li>\n<li>Tag Minimum\u00a0Value Column<\/li>\n<li>Data Confidence Column<\/li>\n<li>Put Select Query<\/li>\n<li>Put Insert Statement<\/li>\n<li>Put Update Statement<\/li>\n<\/ul>\n<\/td>\n<td>These parameters specify the column names that will define your timeseries query results.<\/p>\n<ul>\n<li>Tag Maximum Value Column \u2013 If not\u00a0provided, the adaptor will return <strong>null<\/strong>\u00a0maximum value.<\/li>\n<li>Tag Minimum\u00a0Value Column\u00a0\u2013 If not\u00a0provided, the adaptor will return <strong>null<\/strong> minimum value.<\/li>\n<li>Data Confidence Column\u00a0\u2013 If not\u00a0provided, the adaptor will return <strong>100<\/strong> as the default confidence\u00a0value.<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2 class=\"page-subheading\">Keywords<\/h2>\n<p class=\"intro-text\">When the Relational Adaptor identifies\u00a0any of these keywords in a\u00a0query, it replaces them with the corresponding request\u2019s field.<\/p>\n<table>\n<tbody>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\"><strong>Keyword<\/strong><\/td>\n<td><strong>Description<\/strong><\/td>\n<td><strong>Narrow or Wide<\/strong><\/td>\n<td><strong>Read or Write<\/strong><\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">CONFIDENCE()<\/td>\n<td>Confidence of the value that is being inserted\/updated.<\/td>\n<td>Both<\/td>\n<td>Write<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">ENDTIME()<\/td>\n<td>The request\u2019s end time.<\/td>\n<td>Both<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">STARTTIME()<\/td>\n<td>The request\u2019s start time.<\/td>\n<td>Both<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">TAGNAME()<\/td>\n<td>Name of the tag that is being inserted\/updated in a narrow table.<\/td>\n<td>Narrow<\/td>\n<td>Write<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">TAGLIST()<\/td>\n<td>The request\u2019s entity names. Used in Narrow query requests.<\/td>\n<td>Narrow<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">TAGFIELD()<\/td>\n<td>Name of the column\/field that is being inserted\/updated in a wide table.<\/td>\n<td>Wide<\/td>\n<td>Write<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">TAGFIELDLIST()<\/td>\n<td>The request\u2019s entity names (translated to field names). Used in Wide query requests.<\/td>\n<td>Wide<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">NAMELIST()<\/td>\n<td>The request\u2019s entity names. Used in Wide query requests.<\/td>\n<td>Wide<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">NAME()<\/td>\n<td>Name of the entity whose value is being inserted\/updated in a wide table.<\/td>\n<td>Wide<\/td>\n<td>Write<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">SAMPLEINTERVAL()<\/td>\n<td>Sample interval of the get request as a number of seconds (it can have a fractional part, so a half second interval would be passed to the query as 0.5).<\/td>\n<td>Both<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">SAMPLEMETHOD()<\/td>\n<td>Sample method of the get request as a string.<\/td>\n<td>Both<\/td>\n<td>Read<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">TIMESTAMP()<\/td>\n<td>Timestamp of the value that is being inserted\/updated.<\/td>\n<td>Both<\/td>\n<td>Write<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">VALUE()<\/td>\n<td>The value that is being inserted\/updated.<\/td>\n<td>Both<\/td>\n<td>Write<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2 class=\"page-subheading\">Example Query using Column Parameters<\/h2>\n<pre class=\"\">SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_METRICS.PRODUCTION_DATE,\r\n  PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL, PRV_PRODUCTION_METRICS.CONFIDENCE, \r\n  PRV_PRODUCTION_METRICS.MIN, PRV_PRODUCTION_METRICS.MAX\r\nFROM PRV_PRODUCTION_METRICS, PRV_PRODUCTION_HIERARCHIES\r\nWHERE PRV_PRODUCTION_METRICS.PRODUCTION_ENTITY_ID = PRV_PRODUCTION_HIERARCHIES.PRODUCTION_ENTITY_ID\r\nAND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())\r\nAND PRV_PRODUCTION_METRICS.PRODUCTION_DATE BETWEEN TRUNC(STARTTIME()) \r\nAND TRUNC(ENDTIME())\r\nORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC\r\n<\/pre>\n<p>Where:<\/p>\n<p>Tag Name Column =\u00a0AREA_NAME<br \/>\nData Timestamp Column =\u00a0PRODUCTION_DATE<br \/>\nData Value Column =\u00a0PROD_OIL_ALLOC_GROSS_VOL<br \/>\nData Confidence Column =\u00a0CONFIDENCE<br \/>\nTag Minimum Value Column = MIN<br \/>\nTag Maximum Value Column = MAX<\/p>\n<p>It is important that you order the returned results in date order, oldest time first.<\/p>\n<h2 class=\"page-subheading\">PUT Queries<\/h2>\n<p class=\"intro-text\">There are 3 main rules for being able to write values back to a source database.<\/p>\n<ol class=\"intro-text\">\n<li>\u2018Allow Write\u2019 must be set to True.<\/li>\n<li>The available tags\u00a0query must contain the value column with the correct data type.<\/li>\n<li>Run <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/connecting-your-data\/time-series-tag-fetch\/\">Tag Discovery<\/a> at least once with such an available tags query.<\/li>\n<\/ol>\n<p class=\"intro-text\"><strong>About the Available Tags Query<\/strong><\/p>\n<p class=\"intro-text\">The Available Tags Query (also known as the Discovery Query) is used to return a list of tags. It must always contain the <em>Value<\/em> column, even for narrow queries.\u00a0This is because the adaptor will store the Value column\u2019s <em>type<\/em> in the native names of tags, so that the adaptor will know how to convert incoming values to what the database expects.<\/p>\n<p class=\"intro-text\">This has 2 important consequences:<\/p>\n<ul class=\"intro-text\">\n<li>If the Value column is not part of the query, the type would be missing from the native name. When an adaptor receives a Put request for a tag which doesn\u2019t have the data type stored in its native name, it throws an <em>InvalidConfigurationException<\/em>. Therefore, if you had an existing read-only datasource that you want to turn into a read-write datasource, you must update the query and run the available tags query again, so that the adaptor can store the required types in the native names.<\/li>\n<\/ul>\n<ul class=\"intro-text\">\n<li>If the adaptor cannot determine the type of a column (e.g. sql_variant type) or it determines the type incorrectly (as can happen with ODBC), you can override the column type in the available tags query to provide the adaptor with instructions on how to handle the specific column. To do this, use the CONVERT() function (if using MSSQL) or the CAST() function (if using Oracle). <br \/>\nExamples: <br \/>\nSELECT \u2026, CONVERT(int, SqlVariantValueColumn)\u2026<br \/>\nSELECT \u2026, CAST(VALUE_COLUMN AS date) \u2026<\/li>\n<\/ul>\n<p class=\"intro-text\">If writing values is not required, then the <em>Value<\/em> column can be omitted from narrow queries.<\/p>\n<h2 class=\"page-subheading\">Narrow Queries<\/h2>\n<p class=\"intro-text\">A relational query can be in one of two forms: Narrow or Wide. Narrow queries return a single Value column.\u00a0If you configure your datasource\u2019s query type to <strong>Narrow<\/strong>, you need to provide the Value column in your query.<\/p>\n<p class=\"intro-text\">E.g. In the following query,\u00a0PROD_OIL_ALLOC_GROSS_VOL is the Value column:<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT PRV_PRODUCTION_METRICS.NAME, PRV_PRODUCTION_METRICS.PROD_DATE, \r\n   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL\r\nFROM PRV_PRODUCTION_METRICS \r\nWHERE PRV_PRODUCTION_METRICS.NAME IN (NAMELIST())\r\nORDER BY PRV_PRODUCTION_METRICS.PROD_DATE ASC<\/pre>\n<p class=\"intro-text\">Here are some more examples.<\/p>\n<h3>Available Tags Query<\/h3>\n<p class=\"intro-text\">These queries returns a distinct list of tag names.<\/p>\n<pre>SELECT DISTINCT TagnameColumn \r\nFROM NarrowTable\r\n\r\n\r\nSELECT DISTINCT PRV_PRODUCTION_HIERARCHIES.AREA_NAME\r\nFROM PRV_PRODUCTION_HIERARCHIES<\/pre>\n<h3>Single Point Raw Query<\/h3>\n<p class=\"intro-text\">These are examples of single point raw query using TAGLIST() and STARTTIME() keywords.<\/p>\n<p>These will return the latest value available for the request tag that is less than or equal to the request time.<\/p>\n<p>If an exact date match is used, you can end up getting no results return unless the date picker in explorer is set to the exact time as the value in the database.<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT TagnameColumn, TimestampColumn, ValueColumn, ConfidenceColumn\r\nFROM NarrowTable \r\nWHERE TagnameColumn IN (TagList()) \r\nAND TimestampColumn = \r\n( \r\n  SELECT MAX(InnerTable.TimestampColumn) \r\n  FROM NarrowTable InnerTable\r\n  WHERE InnerTable.TagnameColumn = NarrowTable.TagnameColumn \r\n  AND InnerTable.TimestampColumn &lt;= StartTime()\r\n)\r\n\r\nSELECT PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE, SUM(PRVPM.PROD_OIL_ALLOC_GROSS_VOL) \r\nAS PROD_OIL_ALLOC_GROSS_VOL\r\nFROM PRV_PRODUCTION_METRICS PRVPM, PRV_PRODUCTION_HIERARCHIES PRVPH,\r\n   (SELECT PRVPHMT.AREA_NAME AS NAME,MAX(PRVPMMT.PRODUCTION_DATE) AS MAX_TIMESTAMP\r\n   FROM PRV_PRODUCTION_METRICS PRVPMMT, PRV_PRODUCTION_HIERARCHIES PRVPHMT\r\n   WHERE PRVPMMT.PRODUCTION_ENTITY_ID = PRVPHMT.PRODUCTION_ENTITY_ID\r\n   AND PRVPHMT.AREA_NAME IN (TAGLIST())\r\n   AND PRVPMMT.PRODUCTION_DATE &lt;= STARTTIME()\r\n   AND PRVPMMT.READING_TYPE = 'DAILY'\r\n   GROUP BY PRVPHMT.AREA_NAME) PRVPMMAXTIME\r\nWHERE PRVPM.PRODUCTION_ENTITY_ID = PRVPH.PRODUCTION_ENTITY_ID\r\nAND PRVPM.PRODUCTION_DATE = PRVPMMAXTIME.MAX_TIMESTAMP\r\nAND PRVPH.AREA_NAME = PRVPMMAXTIME.NAME\r\nAND PRVPH.AREA_NAME IN (TAGLIST())\r\nGROUP BY PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE<\/pre>\n<h3>Historical Raw Query<\/h3>\n<p class=\"intro-text\">This is an example of a historical raw query using TAGLIST(), STARTTIME(), and ENDTIME() keywords.<\/p>\n<pre class=\"\">SELECT TagnameColumn, TimestampColumn, ValueColumn, ConfidenceColumn \r\nFROM NarrowTable \r\nWHERE TagnameColumn IN (TagList()) \r\nAND TimestampColumn BETWEEN StartTime() AND EndTime() \r\nORDER BY TimestampColumn ASC\r\n\r\nSELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_METRICS.PRODUCTION_DATE, \r\n   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL\r\nFROM PRV_PRODUCTION_METRICS, PRV_PRODUCTION_HIERARCHIES\r\nWHERE PRV_PRODUCTION_METRICS.PRODUCTION_ENTITY_ID = PRV_PRODUCTION_HIERARCHIES.PRODUCTION_ENTITY_ID\r\nAND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (TAGLIST())\r\nAND PRV_PRODUCTION_METRICS.PRODUCTION_DATE BETWEEN TRUNC(STARTTIME()) \r\nAND TRUNC(ENDTIME())\r\nORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC<\/pre>\n<h3>Put Select Query<\/h3>\n<p class=\"intro-text\">In this type of query, the adaptor does not care about the actual value in the database. It just has to know whether it exists or not. Whether you use \u201cSELECT NULL\u201d or \u201cSELECT Value()\u201d or \u201cSELECT \u2018Wednesday\u2019\u201d doesn\u2019t matter. The adaptor never inspects the returned value. It just checks whether the query returned any row or not so that it can decide whether to execute an INSERT or an UPDATE statement.<\/p>\n<pre>SELECT NULL\r\nFROM NarrowTable\r\nWHERE TagnameColumn = TagName()\r\nAND TimestampColumn = Timestamp()<\/pre>\n<h3>Put Insert Statement<\/h3>\n<pre>INSERT INTO NarrowTable(TagnameColumn, TimestampColumn, ConfidenceColumn, ValueColumn)\r\nVALUES (TagName(), Timestamp(), Confidence(), Value())<\/pre>\n<h3>Put Update Statement<\/h3>\n<pre>UPDATE NarrowTable\r\nSET ConfidenceColumn = Confidence(), ValueColumn = Value()\r\nWHERE TagnameColumn = TagName()\r\nAND TimestampColumn = Timestamp()<\/pre>\n<p>&nbsp;<\/p>\n<h2 class=\"page-subheading\">Wide Queries<\/h2>\n<p class=\"intro-text\">Wide queries return more than one Value column.<\/p>\n<p class=\"intro-text\">E.g. In the following wide query, PROD_OIL_ALLOC_GROSS_VOL and\u00a0PROD_OIL_TARGET_GROSS_VOL are the Value columns:<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT PRV_PRODUCTION_METRICS.NAME AS NAME, PRV_PRODUCTION_METRICS.PROD_DATE AS TIMESTAMP, \r\n   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL, PRV_PRODUCTION_METRICS.PROD_OIL_TARGET_GROSS_VOL\r\nFROM PRV_PRODUCTION_METRICS\r\nWHERE PRV_PRODUCTION_METRICS.NAME IN (NAMELIST())\r\nORDER BY PRV_PRODUCTION_METRICS.PROD_DATE ASC<\/pre>\n<p class=\"intro-text\">Here are some more examples.<\/p>\n<h3>Available Tags Query<\/h3>\n<p class=\"intro-text\">This query returns a list of tags.<\/p>\n<pre>SELECT DISTINCT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, 0 \r\nAS PROD_OIL_ALLOC_GROSS_VOL, 0 AS PROD_OIL_TARGET_GROSS_VOL\r\nFROM PRV_PRODUCTION_HIERARCHIES\r\nWHERE PRV_PRODUCTION_HIERARCHIES.AREA_NAME IS NOT NULL<\/pre>\n<h3>Single Point Query<\/h3>\n<p class=\"intro-text\">This is an example of a single point query using NAMELIST() and STARTTIME() keywords.<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE,\r\nSUM(PRVPM.PROD_OIL_ALLOC_GROSS_VOL) AS PROD_OIL_ALLOC_GROSS_VOL,\r\nSUM(PRVPM.PROD_OIL_TARGET_GROSS_VOL) AS PROD_OIL_TARGET_GROSS_VOL,\r\nSUM(PRVPM.PROD_GAS_ALLOC_GROSS_VOL) AS PROD_GAS_ALLOC_GROSS_VOL,\r\nSUM(PRVPM.PROD_GAS_TARGET_GROSS_VOL) AS PROD_GAS_TARGET_GROSS_VOL,\r\nFROM PRV_PRODUCTION_METRICS PRVPM, PRV_PRODUCTION_HIERARCHIES PRVPH,\r\n  (SELECT&amp;nbsp;PRVPHMT.AREA_NAME AS NAME, \r\n   MAX(PRVPMMT.PRODUCTION_DATE) AS MAX_TIMESTAMP FROM PRV_PRODUCTION_METRICS PRVPMMT, \r\n   PRV_PRODUCTION_HIERARCHIES PRVPHMT\r\n  WHERE&amp;nbsp;PRVPHMT.AREA_NAME IN (NAMELIST())\r\n  AND PRVPMMT.PRODUCTION_DATE &lt;= TRUNC(STARTTIME())\r\n  GROUP BY PRVPHMT.AREA_NAME) PRVPMMAXTIME\r\nWHERE PRVPM.PRODUCTION_ENTITY_ID = PRVPH.PRODUCTION_ENTITY_ID\r\nAND PRVPM.PRODUCTION_DATE = PRVPMMAXTIME.MAX_TIMESTAMP\r\nAND STARTTIME() &lt; PRVPMMAXTIME.MAX_TIMESTAMP\r\nAND PRVPH.AREA_NAME = PRVPMMAXTIME.NAME\r\nAND PRVPH.AREA_NAME IN (NAMELIST())\r\nGROUP BY PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE<\/pre>\n<h3>Single Point Query<\/h3>\n<p class=\"intro-text\">This is an example of a single point query using TAGFIELDLIST(), NAMELIST(), and STARTTIME() keywords.<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_HIERARCHIES.START_DATE, TAGFIELDLIST()\r\nFROM PRV_PRODUCTION_HIERARCHIES\r\nWHERE PRV_PRODUCTION_HIERARCHIES.START_DATE &gt;= TRUNC(STARTTIME())&amp;nbsp;\r\nAND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())<\/pre>\n<h3>Historical Raw Query<\/h3>\n<p class=\"intro-text\">This is an example of a historical raw query using NAMELIST(), STARTTIME(), and ENDTIME() keywords.<\/p>\n<pre class=\"\">SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_METRICS.PRODUCTION_DATE, \r\n   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL, PRV_PRODUCTION_METRICS.PROD_OIL_TARGET_GROSS_VOL\r\nFROM PRV_PRODUCTION_METRICS, PRV_PRODUCTION_HIERARCHIES\r\nWHERE PRV_PRODUCTION_METRICS.PRODUCTION_ENTITY_ID = PRV_PRODUCTION_HIERARCHIES.PRODUCTION_ENTITY_ID\r\nAND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())\r\nAND PRV_PRODUCTION_METRICS.PRODUCTION_DATE BETWEEN STARTTIME()\u00a0AND ENDTIME()\r\nORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC<\/pre>\n<h3>Historical Raw Query<\/h3>\n<p class=\"intro-text\">This is an example of a historical raw query using NAMELIST(), TAGFIELDLIST(), STARTTIME(), and ENDTIME() keywords.<\/p>\n<pre class=\"lang:tsql decode:true \">SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_HIERARCHIES.START_DATE, TAGFIELDLIST()\r\nFROM PRV_PRODUCTION_HIERARCHIES\r\nWHERE PRV_PRODUCTION_HIERARCHIES.START_DATE BETWEEN STARTTIME() AND ENDTIME())\r\nAND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())\r\nORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC<\/pre>\n<h3>Put Select Query<\/h3>\n<p class=\"intro-text\">In this type of query, the adaptor does not care about the actual value in the database. It just has to know whether it exists or not. Whether you use \u201cSELECT NULL\u201d or \u201cSELECT Value()\u201d or \u201cSELECT \u2018Wednesday\u2019\u201d doesn\u2019t matter. The adaptor never inspects the returned value. It just checks whether the query returned any row or not so that it can decide whether to execute an INSERT or an UPDATE statement.<\/p>\n<pre>SELECT NULL\r\nFROM WideTable\r\nWHERE TagnameColumn = Name()\r\nAND TimestampColumn = Timestamp()<\/pre>\n<h3>Put Insert Statement<\/h3>\n<pre>INSERT INTO WideTable (TagnameColumn, TimestampColumn, ConfidenceColumn, TagField())\r\nVALUES (Name(), Timestamp(), Confidence(), Value())<\/pre>\n<h3>Put Update Statement<\/h3>\n<pre>UPDATE WideTable\r\nSET ConfidenceColumn = Confidence(), TagField() = Value()\r\nWHERE TagnameColumn = Name()\r\nAND TimestampColumn = Timestamp()<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article describes the important parameters when configuring a datasource to use the Relational Adaptor to read and write data. It also describes the difference between narrow and wide queries, and provides several examples of each. <\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/primers\/datasources\/writing-queries-for-the-relational-adaptor\/\">Read more<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":5389,"parent":3668,"menu_order":20,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":"","_members_access_role":[],"_members_access_error":""},"categories":[4],"tags":[321,129,320,322],"class_list":["post-15258","page","type-page","status-publish","has-post-thumbnail","hentry","category-explainer","tag-narrow-queries","tag-queries","tag-relational-adaptor","tag-wide-queries","Product-srv"],"_links":{"self":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/15258","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/comments?post=15258"}],"version-history":[{"count":6,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/15258\/revisions"}],"predecessor-version":[{"id":72652,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/15258\/revisions\/72652"}],"up":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/3668"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/media\/5389"}],"wp:attachment":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/media?parent=15258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/categories?post=15258"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/tags?post=15258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}