Invantive SQL Grammar

sqlBatch:

Compatibility

The Invantive implementation of SQL is based upon ANSI SQL, extended by aspects from popular SQL implementations such as PostgreSQL, MySQL, Oracle, Teradata and Microsoft SQL Server. It is topped of with Invantive-specific extensions, especially for procedural SQL, distributed SQL and distributed transactions. The basis is to implement functions such that as little as possible changes are necessary to run a SQL statement originating from another SQL implementation on Invantive SQL. For instance, to retrieve the current time you can use 'sysdate', 'now', 'getdate()' and 'sysdatetime' to name a few. The same holds for the procedural extension Invantive Procedural SQL, which reflects SQL/PSM and makes it easy to port Oracle PL/SQL or PostgreSQL PL/pgSQL statements.

Distributed SQL, Databases and Data Containers

It is easy to exchange and/or combine data across the supported platforms with data. To each platform (such as Salesforce or Exact Online Belgium) multiple connections can be active with the same or different platform-specific connection settings. Each open connection to a platform is named a 'data container'.

All opened connections together are named a 'database'.

When multiple data containers have been opened, each one has an alias to refer it by in Invantive SQL statements. For instance, a connection can be open for two different customer accounts on Exact Online Netherlands aliased as 'eolnl_comp1' and 'eolnl_comp55') and one for an Exact Online Belgium custom, aliased as 'eolbe_my_new_company'. The aliases can be freely chosen as long as they are valid identifiers and defined in the databases configuration file 'settings.xml'.

Service Providers

A number of special connections are always made, each of which can occur at most once. These are the 'service providers' such as:

• 'datadictionary': metadata of the current database, such as list of tables and executed SQL statements performance.
• 'os': information on the operating system running the SQL engine, such as reading file contents.
• 'smtp': synchronously send mails through SMTP.

Partitioning

Especially online platforms have a multi-tenant structure, in which the data is partitioned per customer, company or person. When the data model is identical across tenants, Invantive SQL considers them 'partitions'. SQL statements can run across multiple or one partitions, often in parallel. This enables consolidation scenarios across partitions (such as Exact Online or Nmbrs companies) as well as high-performance in MPP environments.

The partitions to be used can be specified with the 'use' statement, either through an explicit list of partitions to be selected across data containers, or through a SQL select statement returning the list of partitions to use. Please note that although the 'use' statement resembles the 'use DATABASE' statement on Microsoft SQL Server or PostgreSQL you can on Invantive SQL have multiple partitions active at the same time in one user session.

Identifiers

For identifiers, the regular conventions hold for the set of allowed characters. Depending on the platform, the identifiers are case sensitive or not. In general, it is best to assume that the identifier are case insensitive. There is no length limit on an identifier imposed by Invantive SQL.

Procedural SQL

Invantive Procedural SQL (or "PSQL" for short) is a procedural extension on top of Invantive SQL. It is based on the ISO-standard 9075-4:2016 (SQL/PSM) and extends Invantive SQL with procedural options like blocks, variables, conditional execution and loops. The procedural code is - together with the Invantive SQL contained - as a whole into pseudo-code and then executed.

The procedural code does not lean on the procedural options of the platforms being used, so it is easy to retrieve and change data in all supported cloud, file and database platforms. The pre-compiled procedural code does not perform context switches between procedural and SQL logic.

Licensing

Features

The available functionality of Invantive SQL features is based upon the licensed features. For instance the free implementation of Invantive SQL is limited to 15.000 rows and no access to group functions. Please consult the data dictionary contents for your license features.

Usage Fees

For paid products, the fee depends on a number of factors: users, devices and billable partitions. All fees depend on actual use. Additionally, the number and volume in KB of reads and writes is registered to enable communication with the platform partners on the performance of their platform.

A rough estimate is that the billable partitions reflect the product of number of legal entities times number of source systems. The number of billable partitions is determined as follows, where using twice or more often the same billable partition doesn't influence the number:

• Accessing a provider which does not have a concept of "partition" typically counts as one partition per different connection. A so-called data container ID is determined which reflects the backend connected to. For instance, connecting to my-ftp-host.com and another-ftp-host.com counts as two partitions. Examples of such providers are FTP, SQL Server and cbs.nl.
• Accessing a provider which has a concept of "partition" counts as the number of partitions used during the connection. For instance, using the Exact Online company 234 and 456 counts as two partitions. Examples of such providers are Exact Online, NMBRS, Loket and XML Audit File Financieel.
• Accessing data through xmltable, csvtable, internettable, jsontable and exceltable counts as the number of different parameter combination used. For instance, accessing an Excel range 'Sheet1' and a named range 'mydata' counts as two partitions.

settings.xml

The file settings.xml defines for a user or program the list of defined databases. Databases are grouped in 'database groups' for visual display. Database groups have no further functionality. Each database consists of one or multiple data containers.

The file 'settings.xml' is most often found on Microsoft Windows in your '%USERPROFILE%\invantive' folder, such as 'c:\users\john.doe\invantive\settings.xml'. It is shared across all Invantive SQL product installations for the user.

There are many scenarios to share database specifications across a user community, such as WAN-scenarios with Invantive Web Service, large corporate scenarios using DNS-entries as well as file shares, included files as well as single user solutions. Please involve a consultant when you want to deploy across thousands of users or more.

For user communities of up to 10 users, we recommend that company-specific settings are grouped per role in a separate file named 'settings-ROLE.xml' and placed in the default folder. Invantive SQL will automatically merge these files in the main settings.xml file.

Group Functions

The Invantive implementation of SQL is based upon ANSI SQL, extended by aspects from popular SQL implementations such as PostgreSQL, MySQL, Oracle, Teradata and Microsoft SQL Server. It is topped of with Invantive-specific extensions, especially for distributed SQL and distributed transactions. The basis is to implement functions such that as little as possible changes are necessary to run a SQL statement designed for use with another SQL implementation on Invantive SQL. For instance, to retrieve the current time you can use 'sysdate', 'now', 'getdate()' and 'sysdatetime' to name a few.

Popular group functions such as 'stddev' are available. However, currently you can not combine in one unnested SQL statement both group functions as well as expressions on the variables. In that case use an inner (nested) SQL statement to apply the expressions on the data, and execute the group functions in the outer SQL statement with the syntax 'select group() from ( select ... from ... )'.

Locking

An Invantive SQL statement can work with many traditional and online platforms. There are no locking features on data and objects, since few online and traditional platforms connected provide these and the typical use of distributed transactions leave even less opportunity for data and object locking.

Transactions

Invantive SQL has limited support for transactions. DML is forwarded to a platform and depending on the platform an error can cause part of the work to be registered or everything to be rolled back. Within the SQL engine, multiple changes can be collected and forwarded to the platform at once. For instance, when creating an EDIFACT message you need to combine an invoice header with invoice lines into one EDIFACT message. Collection of multiple changes is done using the 'identified by' and 'attach to' syntax, optionally preceded by 'begin transaction'.

Format Masks

Operations such as to_char and to_date support a limited number of format masks:

• YYYY: 4-digit year.
• YYYYMM: 4-digit year, plus 2-digit number month within year.
• YYYYMMDD: 4-digit year, 2-digit month number within year, plus 2-digit day number within month.
• YYYYMMDDHH24MI: 4-digit year, 2-digit month number within year, 2-digit day number within month, 2-digit 24-hour clock hour number, plus 2-digit minutes number within hour.
• YYYYMMDDHH24MISS: 4-digit year, 2-digit month number within year, 2-digit day number within month, 2-digit 24-hour clock hour number, 2-digit minutes number within hour, plus 2-digit seconds number within minute.
• YYYY-MM-DD: 4-digit year, dash ('-'), 2-digit month number within year, dash ('-'), plus 2-digit day number within month.
• YYYY-MM-DD HH24:MI:SS: 4-digit year, dash ('-'), 2-digit month number within year, dash ('-'), plus 2-digit day number within month, 2-digit hour on 24 hour clock, colon (':'), 2-digit minutes within hour, colon (':'), 2-digit seconds within minutes.
• YYYY/MM/DD: 4-digit year, slash ('/'), 2-digit month number within year, slash ('/'), plus 2-digit day number within month.
• YYYY/MM/DD HH24:MI:SS: 4-digit year, slash ('/'), 2-digit month number within year, slash ('/'), plus 2-digit day number within month, 2-digit hour on 24 hour clock, colon (':'), 2-digit minutes within hour, colon (':'), 2-digit seconds within minutes.
• MMDDYY: 2-digit month number within year, 2-digit day number within month, plus 2-digit year.
• MMDDYYYY: 2-digit month number within year, 2-digit day number within month, plus 4-digit year.
• DDMMYYYY: 2-digit day number within month, 2-digit month number within year, 4-digit year.
• DD-MM-YY: 2-digit day number within month, dash ('-'), 2-digit month number within year, dash ('-'), 2-digit year.
• DD-MMM-YY: 2-digit day number within month, dash ('-'), short month name, dash ('-'), 2-digit year.
• DD/MM/YY: 2-digit day number within month, slash ('/'), 2-digit month number within year, slash ('/'), 2-digit year.
• DD/MMM/YY: 2-digit day number within month, slash ('/'), short month name, slash ('/'), 2-digit year.
• DD-MM-YYYY: 2-digit day number within month, dash ('-'), 2-digit month number within year, dash ('-'), 4-digit year.
• DD-MMM-YYYY: 2-digit day number within month, dash ('-'), short month name within year, dash ('-'), 4-digit year.
• DD/MM/YYYY: 2-digit day number within month, slash ('/'), 2-digit month number within year, slash ('/'), 4-digit year.
• DD/MMM/YYYY: 2-digit day number within month, slash ('/'), short month name within year, slash ('/'), 4-digit year.
• DD-MM-YYYY HH24:MI:SS: 2-digit day number within month, dash ('-'), 2-digit month number within year, dash ('-'), 4-digit year, space (' '), 2-digit hour on 24 hour clock, colon (':'), 2-digit minutes within hour, colon (':'), 2-digit seconds within minutes.
• DD/MM/YYYY HH24:MI:SS: 2-digit day number within month, slash ('/'), 2-digit month number within year, slash ('/'), 4-digit year, space (' '), 2-digit hour on 24 hour clock, colon (':'), 2-digit minutes within hour, colon (':'), 2-digit seconds within minutes.
• DD/MMM/YYYY HH24:MI:SS: 2-digit day number within month, slash ('/'), short month name within year, slash ('/'), 4-digit year, space (' '), 2-digit hour on 24 hour clock, colon (':'), 2-digit minutes within hour, colon (':'), 2-digit seconds within minutes.
• YYYYIW: 4-digit year, plus ISO week number.
• YYYY-IW: 4-digit year, dash ('-'), plus ISO week number.
• IW: ISO week number.
• D: day of week according to ISO 8601.
• MMMM DD, YYYY HH24:MI TT: long month, 2-digit day number within month, comma (','), space (' '), 4-digit year number, space (' '), 2-digit hour on 24 hour clock, colon (':'), 2-digit minute within hour, space (' '), time zone.

Pointers

The following list of pointers provides you with samples and instructions:

• Invantive SQL wiki: https://invantive.atlassian.net/wiki/spaces/SUP/overview
• Blog articles: https://documentation.invantive.com/notes/webhelp/index.html.

Release History

The following significant changes were made to the Invantive SQL grammar:

• Release 17.29 (BETA) adds:
• 20181031: allow named expressions as table function parameters, using => as association operator.
• 20181123: add WHEN EXCEPTION to PSQL BEGIN..END block, with exception being WHEN OTHER or WHEN exception name.
• 20181123: extend SYNCHRONIZE statement.
• 20181224: extend SYNCHRONZIE statement with IGNORE NULLS clause.
• Release 17.30 (Production): All changes sofar.
• Release 17.31 (BETA) adds:
• 20190116: addition of language code parameter to TRANSLATE_RESOURCE.
• 20190117: addition of IS_NUMBER function.
• 20190124: addition of TO_BOOLEAN, IS_BOOLEAN, IS_DATE, IS_GUID functions.
• Release 17.32 (Production): All changes sofar.
• Release 17.33 (BETA) adds:
• 20190131: texts as partition names .
• 20190310: expression as billing id and reference.
• 20190322: select all columns 'except' syntax.
• 20190401: months_between SQL function.
• 20190401: excel_day SQL function.
• 20190403: sqlrowcount, sqlerrm and sqlcode functions.
• 20190410: 'exists' expression.
• 20190601: 'when obsolete within' variant of 'alter persistent cache refresh'.
• 20190605: gzip and ungzip functions.
• 20190624: 'alter session set roles' syntax for connectors with row-level security.
• 20190701: ascii_to_blob and unicode_to_blob functions.
• 20190706: 'alter session set iuid source' syntax.
• 20190821: add htmltable.
• 20191007: add 'except' to synchronize statement for insert and update.
• 20191008: add 'ignore changes to' to synchronize statement for update.
• 20191209: multiple PSQL batches in one statement.
• 20200102: add internettable.
• Release 17.34 (Production): All changes sofar.

A SQL batch consists of one or more Invantive SQL and/or PSQL statements separated by the semi-colon batch separator (';').

sqlBatch ::= sqlOrPSqlStatement ( BATCHSEPARATOR sqlOrPSqlStatement )* BATCHSEPARATOR?

no references

sqlOrPSqlStatement:

A number of SQL and PSQL statements can be used to compose a batch.

sqlOrPSqlStatement
         ::= sqlStatement
           | pSqlStatement
           | pSqlBlockNoBatchSeparator
           | pSqlCreateFunction
           | pSqlCreateProcedure
           | pSqlAlterFunction
           | pSqlAlterProcedure
           | pSqlDropFunction
           | pSqlDropProcedure

referenced by:

• sqlBatch

sqlStatement:

An Invantive SQL statement can retrieve or exchange data for many traditional and online platforms. Many platforms also support the use of DML (Data Manipulation Language) statements to change the data contained. On a few platforms you can execute DDL (Data Definition Language) statements to create new data structure or objects such as tables, procedures or sequences.

Popular Invantive SQL extensions are the synchronize statement to make sure multiple data sources contain the same data and the use statement to run SQL across multiple partitions.

sqlStatement
         ::= selectStatement
           | insertStatement
           | updateStatement
           | deleteStatement
           | ddlStatement
           | setStatement
           | useStatement
           | transactionStatement
           | executeFileStatement
           | synchronizeStatement

referenced by:

• pSqlStatement
• sqlOrPSqlStatement

selectStatement:

A SQL select statement retrieves data from one or multiple data containers. A select statement can be composed of multiple data sets retrieved from many platforms, combined by set operators such as 'union'.

Often the performance of cloud platforms is less than traditional database platforms. With the 'limit' clause a limited number of rows can be retrieved quickly from a table or view after applying sorting as specified by the possibly present 'order by'. An alternative for a 'limit' clause is to use the 'top' clause.

A sequence of Invantive SQL statements, separated by the semi-colon separator character.

Each statement in the SQL batch will be executed consecutively. Execution will be stopped when an error occurs during execution of a statement.

selectStatement
         ::= uniqueSelectStatement setOperatorSelectStatement* orderBy? limitClause?

referenced by:

• arithmeticExpression
• createTableStatement
• embeddedSelect
• existsExpression
• inSelectStatement
• insertStatement
• pSqlForRecordLoopStatement
• sqlStatement
• useStatement

inSelectStatement:

A SQL select statement retrieves data from one or multiple data containers. This variant makes this data available to a containing SQL select statement. This feature is also known as an 'inline view'.

inSelectStatement
         ::= selectStatement

referenced by:

• predicateExpression

setOperatorSelectStatement:

SQL is based upon a solid mathematical foundation named 'set theory' with some exceptions. The set operators of Invantive SQL enable you to combine sets of data sets such as merging two sets of data. Please note that SQL actually uses 'bags', which opposed to 'sets', allow duplicates. To change bags of data into sets, either use 'distinct' or the 'union' set operator without 'all'. In general, the extensive use of 'distinct' signals bad database design.

The 'union' set operator returns the union of the data on the left and right side of the union while removing duplicate rows. The 'union all' set operator returns the union of the data on the left and right side of the union without removing duplicate rows. The 'minus' set operator returns all rows from the left side which do not occur in the right side. The 'intersect' set operator returns all rows that occur both in the left and right side.

The 'union' set operator has an extension to facilitate sieving overlapping data sets of decreasing quality using 'distinct on'. The 'union distinct on' set operator returns the union of the data sets listed, but only the first match on the column list is returned. The first match is defined as a fallthrough from left to right. In general, the preferred source of a set of data is listed first, followed by one or multiple 'union distinct on' set operators, each with a data set of less preference.

setOperatorSelectStatement
         ::= ( UNION ( ALL | DISTINCT ON columnList )? | MINUS_C | INTERSECT ) uniqueSelectStatement

referenced by:

• selectStatement

uniqueSelectStatement:

Retrieves a data set from one or more data containers.

uniqueSelectStatement
         ::= select executionHints? distinct? topClause? selectList ( into pSqlVariableList )? ( FROM dataSource joinStatements? whereClause? )? groupBy?

referenced by:

• selectStatement
• setOperatorSelectStatement

dataSource:

A data source can be a table, a table with parameters or a nested select (an 'inline view'). Also, a data source can be a binary or text string being interpreted as XML, CSV, JSON or binary Excel Open XML format.

dataSource
         ::= ( tableOrFunctionSpec | embeddedSelect | xmlTableSpec | csvTableSpec | jsonTableSpec | excelTableSpec | htmlTableSpec | stringSplitSpec | internetTableSpec ) aliased? pivotClause?

referenced by:

• joinStatement
• uniqueSelectStatement

select:

select   ::= SELECT

referenced by:

• uniqueSelectStatement

executionHints:

Execution hints allow you to control individually the execution of SQL statements. Whenever possible, the hints will be used. In contrary to other platforms, Invantive SQL requires a hint to be valid according to the grammar when specified. This reduces the engineering risk that hints become invalid by accident.

executionHints
         ::= EXECUTION_HINT_START ( joinSet | noJoinSet | ods | resultSetName | resultSetSerialization | lowCost | httpDiskCache | httpMemoryCache )* EXECUTION_HINT_END

referenced by:

• uniqueSelectStatement

httpDiskCache:

The http_disk_cache-hint specifies whether messages may be cached on disk when the provider uses HTTP to exchange data with the backing platform. This typically holds only for cloud-based platforms such as Exact Online, Teamleader or Salesforce. The default setting is false. The first parameter is a boolean whether data may be taken from the disk cache, the second parameter is a boolean whether data retrieved must be stored also in the disk cache and the third parameter is an integer that specifies the number of seconds before a disk cache hit found is to considered stale.

The use of the http_disk-cache-hint is recommended for data which is known to change seldom such as seeded or reference data. The contents of the disk cache are persistent across Invantive SQL sessions.

The disk cache is located in the Cache folder of the Invantive configuration folder.

httpDiskCache
         ::= HTTP_DISK_CACHE ( PARENTHESIS_OPEN booleanConstant ( COMMA booleanConstant ( COMMA intervalConstant )? )? PARENTHESIS_CLOSE )?

referenced by:

• executionHints

httpMemoryCache:

The http_memory_cache-hint specifies whether messages may be cached in memory when the provider uses HTTP to exchange data with the backing platform. This typically holds only for cloud-based platforms such as Exact Online, Teamleader or Salesforce. The default setting is false. The first parameter is a boolean whether data may be taken from the memory cache, the second parameter is a boolean whether data retrieved must be stored also in the memory cache and the third parameter is an integer that specifies the number of seconds before a memory cache hit found is to considered stale.

The use of the http_memory-cache-hint is recommended for data which is known to change seldom such as seeded or reference data. The contents in the memory cache are forgotten across Invantive SQL sessions.

The memory cache is located in the Cache folder of the Invantive configuration folder.

httpMemoryCache
         ::= HTTP_MEMORY_CACHE ( PARENTHESIS_OPEN booleanConstant ( COMMA booleanConstant ( COMMA intervalConstant )? )? PARENTHESIS_CLOSE )?

referenced by:

• executionHints

ods:

The ods-hint controls the use of the Invantive Data Cache stored in a relational database. The Invantive Data Cache is also the basis of the Operational Data Store managed by Invantive Data Replicator and the data warehouses managed by Invantive Data Vault. The ods-hint specifies the maximum age data from the data cache eligible for use.

The boolean specifies whether the Data Cache may be used to answer a query. Set it to false to disable use of Data Cache for the duration of the query. Keep it on the default true to use Data Cache.

The interval specifies the period of time during which cached results are considered sufficiently fresh for use, such as '30 minutes'.

When no interval is present, the actual platform is consulted. The default with Invantive Data Cache enabled is to always use the data cache contents when not stale according to the metadata of the data cache. In general, that defaults to a maximum age of 7 days.

When use of data cache is requested and a filter is applied, all data will be retrieved from the actual platform and filtered on the Invantive SQL engine. This also applies when a filter is applied that can be forwarded to the actual platform. When the data is requested a limited number of times and the filter severely reduces the amount of data to be transported, it can be faster to not use the data cache.

ods      ::= ODS ( PARENTHESIS_OPEN booleanConstant ( COMMA intervalConstant )? PARENTHESIS_CLOSE )?

referenced by:

• executionHints

resultSetName:

resultSetName
         ::= RESULT_SET_NAME ( PARENTHESIS_OPEN stringConstant PARENTHESIS_CLOSE )?

referenced by:

• executionHints

resultSetSerialization:

resultSetSerialization
         ::= RESULT_SET_SERIALIZATION ( PARENTHESIS_OPEN ( SHOW | HIDE ) PARENTHESIS_CLOSE )?

referenced by:

• executionHints

joinSet:

Control join approach between two data sources. A column-indexed lookup will be used instead of a full table scan when the number of rows on the left-hand side does not exceed the maximum number of rows specified in the hint. When not specified, a hash lookup will only be used when the number of rows on the left-side does not exceed 5.000.

The actual implementation of a hash lookup depends on the platform on which the data container runs. For instance with OData, a number of requests will be made using an in-construct with a limited number of in-values. With a relation database platform, a native SQL 'in' will be used.

The first identifier is the alias of the table on the right-hand side of the join. The second identifier is the name of the column used to join upon in the right-hand side. The numeric constant specifies upto what number of rows on the left-hand side of the join will allow the join set hint to be used. When the number of rows exceeds the numeric constant, a full table join is made.

The following example takes for instances 5.000 sales invoices from an Exact Online environment with 100.000 sales invoices. Each sales invoice has 4..10 lines. The join does not retrieve all sales invoices nor all invoice lines, but instead fetches the 5.000 sales invoices using the where-clause, and then retrieves the related invoice lines using a column-indexed lookup by invoiceid. Since Exact Online is an OData source, the approximately 30.000 invoice lines will be retrieves in 300 session I/Os each having an in-construct for 100 lines on invoiceid.

joinSet  ::= JOIN_SET PARENTHESIS_OPEN identifier ( COMMA identifier ( COMMA numericConstant )? )? PARENTHESIS_CLOSE

referenced by:

• executionHints

noJoinSet:

The no_join_set hint disables the use of hash-joins. It can be enabled using the join_set hint.

noJoinSet
         ::= NO_JOIN_SET PARENTHESIS_OPEN identifier ( COMMA identifier )? PARENTHESIS_CLOSE

referenced by:

• executionHints

lowCost:

The low_cost-hint specifies that the select with the hint must be considered a select with low execution costs. Low execution costs trigger early evaluation during parsing. By default, select statements using solely in memory storage, dummy and data dictionary are considered low cost and evaluated early. The evaluation of all others is delayed as long as possible.

The use of the low_cost-hint is recommended when the select is used with a 'in ( select ... )' syntax and the developer knows beforehand that it will evaluate fast to values and that the use of these values will allow the use of server-side filtering for the outer select.

lowCost  ::= LOW_COST

referenced by:

• executionHints

distinct:

Addition of the 'distinct' keyword to a SQL select statement de-duplicates the rows returned. Rows are considered duplicates when the values in all selected columns are identical, with two null-values considered equal.

distinct ::= DISTINCT

referenced by:

• avgAggregateFunction
• countAggregateFunction
• listAggAggregateFunction
• productAggregateFunction
• stdDevAggregateFunction
• sumAggregateFunction
• uniqueSelectStatement

topClause:

With the 'top' clause a limited number of rows can be retrieved quickly from a table or view after applying sorting as specified by the possibly present 'order by'.

topClause
         ::= TOP numericConstant

referenced by:

• uniqueSelectStatement

limitClause:

With the 'limit' clause a limited number of rows can be retrieved quickly from a table or view after applying sorting as specified by the possibly present 'order by'.

limitClause
         ::= LIMIT numericConstant

referenced by:

• selectStatement

embeddedSelect:

An embedded select, also known as an 'inline view', retrieves rows using the specified select statement. These rows are consumed by the outer select as were it the results of retrieving the rows from a table.

Invantive SQL does not allow grouping rows with expressions as columns. An embedded select is typically used to evaluate expressions to rows with solely constants. After applying the embedded select the group operators can be applied.

embeddedSelect
         ::= parenthesisOpen selectStatement parenthesisClose

referenced by:

• dataSource

tableSpec:

A table specification without parameters. The optional alias after the at-sign specifies a specific data source to be used, such as 'exactonlinerest..journals@eolbe' specifying the use of Exact Online Belgium when 'eolbe' is associated by the database definitions in settings.xml with Exact Online Belgium.

A number of special so-called 'service providers' are always present, such as 'datadictionary' for use by an alias.

tableSpec
         ::= fullTableIdentifier distributedAliasDirective?

referenced by:

• alterPersistentCacheConfigureWebhooksStatement
• alterPersistentCacheDropStatement
• alterPersistentCacheSetTableOptions
• alterPersistentCacheTableRefreshStatement
• applyToClause
• createTableStatement
• deleteStatement
• dropTableStatement
• insertStatement
• synchronizeStatement
• updateStatement

tableOrFunctionSpec:

A table specification requiring a comma-separated list of parameters to determine the rows to be retrieved.

Traditional SQL syntax did not provide for parameterized queries, matching set theory. Modern variants such as pipelined table functions allow a stored procedure or other imperative language-based approaches to generate rows based upon parameter values. Many data containers support queries that returns rows based upon parameter values. This holds especially for SOAP web services. Table specifications with parameters ease queries on such data containers.

The optional alias after the at-sign specifies a specific data source to be used, such as 'exactonlinerest..journals@eolbe' specifying the use of Exact Online Belgium when 'eolbe' is associated by the database definitions in settings.xml with Exact Online Belgium.

tableOrFunctionSpec
         ::= fullTableIdentifier distributedAliasDirective? tableFunctionSpec? distributedAliasDirective?

referenced by:

• dataSource

tableFunctionSpec:

A list of parameter value expressions identified by position or name to determine the rows to be retrieved by a tableOrFunctionSpec.

tableFunctionSpec
         ::= parenthesisOpen numberedOrNamedExpressionList? parenthesisClose

referenced by:

• tableOrFunctionSpec

numberedOrNamedExpressionList:

numberedOrNamedExpressionList
         ::= ( expression ( COMMA expression )* | namedExpression ) ( COMMA namedExpression )*

referenced by:

• tableFunctionSpec

expressionList:

An ordered comma-separated list of value expressions.

expressionList
         ::= expression ( COMMA expression )*

referenced by:

• functionExpression

namedExpressionList:

An unordered list of value expressions, identified by the parameter name.

namedExpressionList
         ::= namedExpression ( COMMA namedExpression )*

no references

namedExpression:

A value expression, identified by the parameter name, the association operator '=>' and the value expression.

namedExpression
         ::= identifier ASSOCIATION_OPERATOR expression

referenced by:

• namedExpressionList
• numberedOrNamedExpressionList

distributedAliasDirective:

The distributed alias after the at-sign specifies a specific data source to be used, such as 'exactonlinerest..journals@eolbe' specifying the use of Exact Online Belgium when 'eolbe' is associated by the database definitions in settings.xml with Exact Online Belgium.

A number of special so-called 'service providers' are always present, such as 'datadictionary' for use by an alias.

distributedAliasDirective
         ::= AT dataContainerAlias

referenced by:

• partitionIdentifierWithAlias
• setIdentifier
• tableOrFunctionSpec
• tableSpec

dataContainerAlias:

When multiple data containers have been defined in settings.xml for a database, each one is assigned an alias. An alias typically takes the form of a limited number of characters. The presence of an alias allows Invantive SQL to precisely determine to what data container forward a request for data.

dataContainerAlias
         ::= identifier

referenced by:

• alterPersistentCacheRefreshStatement
• distributedAliasDirective

passingSourceOrPathExpression:

The passing option specifies the source of the contents to be interpreted. The contents can be specified as the outcome of an expression such as from a previous read_file() table function, a URL downloaded using httpget() or a string concatenation. The contents can also be specified as to be taken from a specific file identified by it's file name and path as an expression.

passingSourceOrPathExpression
         ::= PASSING FILE? expression

referenced by:

• csvTablePassing
• excelTablePassing
• htmlTablePassing
• jsonTablePassing
• xmlTablePassing

xmlTableSpec:

Data stored in XML format can be interpreted as a data source using the xmltable keyword.

The expression specifies a master XPath expression within the context of which the rows are evaluated using the column specifications.

The passing option specifies the source of the data in XML format. The source is often the outcome of a read_file() table function, a URL download using httpget() or a previous xmlformat() SQL function.

The columns are specified using their XPath relative to the master path.

xmlTableSpec
         ::= XMLTABLE parenthesisOpen ( expression | null )? ( xmlTablePassing | xmlTableLiteral ) xmlTableColumns parenthesisClose

referenced by:

• dataSource

xmlTablePassing:

xmlTablePassing
         ::= passingSourceOrPathExpression

referenced by:

• xmlTableSpec

xmlTableLiteral:

A literal value containing a valid XML document.

xmlTableLiteral
         ::= LITERAL expression

referenced by:

• xmlTableSpec

xmlTableColumns:

A list of XML table column specifications.

xmlTableColumns
         ::= COLUMNS xmlTableColumnSpec ( COMMA xmlTableColumnSpec )*

referenced by:

• xmlTableSpec

xmlTableColumnSpec:

The columns are specified using their XPath relative to the master path.

xmlTableColumnSpec
         ::= identifier dataType PATH stringConstant

referenced by:

• xmlTableColumns

jsonTableSpec:

Data stored in JSON format can be interpreted as a data source using the jsontable keyword.

The expression specifies a master JSON expression within the context of which the rows are evaluated using the column specifications.

The passing option specifies the source of the data in JSON format. The source is often the outcome of a read_file() table function or a URL download using httpget().

The columns are specified using their JSON path relative to the master path.

jsonTableSpec
         ::= JSONTABLE parenthesisOpen ( expression | null )? ( jsonTablePassing | jsonTableLiteral ) jsonTableColumns parenthesisClose

referenced by:

• dataSource

jsonTablePassing:

jsonTablePassing
         ::= passingSourceOrPathExpression

referenced by:

• jsonTableSpec

jsonTableLiteral:

A literal value containing a valid JSON document.

jsonTableLiteral
         ::= LITERAL expression

referenced by:

• jsonTableSpec

jsonTableColumns:

A list of JSON table column specifications.

jsonTableColumns
         ::= COLUMNS jsonTableColumnSpec ( COMMA jsonTableColumnSpec )*

referenced by:

• jsonTableSpec

jsonTableColumnSpec:

The columns are specified using their JSON path relative to the master path.

jsonTableColumnSpec
         ::= identifier dataType PATH stringConstant

referenced by:

• jsonTableColumns

csvTableSpec:

Data stored in CSV format can be interpreted as a data source using the csvtable keyword.

The passing option specifies the source of the data in CSV format. The source is often the outcome of a read_file() table function or a URL download using httpget().

The interpretation process can be controlled on table level using the table options and the specification ends with the CSV columns being mapped to data source columns using their relative position within a row.

csvTableSpec
         ::= CSVTABLE parenthesisOpen ( csvTablePassing | csvTableLiteral ) csvTableOptions csvTableColumns parenthesisClose

referenced by:

• dataSource

csvTableOptions:

The interpretation process can be controlled on table level using the table options.

The row delimiter is a text that separates two CSV rows, such as "chr(13) || chr(10)" or simply a pipe character "'|'". The default is the operating system-specific variant of new line.

The column delimiter is a text that separates two CSV columns such as "';'". The default is comma.

Data in CSV will typically have one or more header CSV rows labeling the individual columns. The 'skip lines' statement excludes the first number of CSV rows from processing.

csvTableOptions
         ::= ( ROW DELIMITER expression )? ( COLUMN DELIMITER expression )? ( SKIP_ LINES expression )?

referenced by:

• csvTableSpec

csvTableLiteral:

A literal value containing a valid CSV document.

csvTableLiteral
         ::= LITERAL expression

referenced by:

• csvTableSpec

csvTablePassing:

csvTablePassing
         ::= passingSourceOrPathExpression

referenced by:

• csvTableSpec

csvTableColumns:

A list of CSV table column specifications.

csvTableColumns
         ::= COLUMNS csvTableColumnSpec ( COMMA csvTableColumnSpec )*

referenced by:

• csvTableSpec

csvTableColumnSpec:

Each CSV column is mapped to a data source column using it's relative position within the CSV row. Position 1 represents the first CSV column.

csvTableColumnSpec
         ::= identifier dataType POSITION numericConstant

referenced by:

• csvTableColumns

excelTableSpec:

Excel file contents in Open XML, such as used with the file extensions 'xlsx' and 'xlsm', can be interpreted as a data source using the exceltable keyword.

The rectangle specifies the rectangular area from which should be taken. Rows in Excel are interpreted as rows from the data source, whereas columns in Excel are interpreted as columns.

The passing option specifies the source of the (binary) contents in zipped Open XML format such as used with the file extensions 'xlsx' and 'xlsm'. The source is often the outcome of a read_file() table function or URL download using httpget().

The interpretation process can be controlled on table level using the table options and the specification ends with the Excel columns being mapped to data source columns using their relative position within the rectangular area.

excelTableSpec
         ::= EXCELTABLE parenthesisOpen excelDataRectangle excelTablePassing excelTableOptions excelTableColumns parenthesisClose

referenced by:

• dataSource

excelDataRectangle:

The rectangle specifies the rectangular area from which data should be taken. Rows in Excel are interpreted as rows from the data source, whereas columns in Excel are interpreted as columns. All cells within the rectangle are considered data; headings should be excluded from the rectangle specification.

A rectangle can be either:

• the contents of a complete worksheet, specified by an expression returning the worksheet name;
• a named range within a specific worksheet, specified by expressions for worksheet name and named range name;
• a cell range identified by an expression with it's dimensions;
• an Excel table identified by an expression with the table name;
• a named range identified by an expression with the named range name.

excelDataRectangle
         ::= ( WORKSHEET ( expression NAME )? | AREA | TABLE | NAME ) expression

referenced by:

• excelTableSpec

excelTablePassing:

excelTablePassing
         ::= passingSourceOrPathExpression

referenced by:

• excelTableSpec

excelTableOptions:

The interpretation process can be controlled on table level using the table options.

Empty rows will typically be found when consuming a rectangular area larger than the actual data, such a complete worksheet. The 'skip empty rows' statement eliminates all completely empty rows from the output.

excelTableOptions
         ::= ( SKIP_ EMPTY ROWS )? ( SKIP_ FIRST numericConstant ROWS )? ( SKIP_ LAST numericConstant ROWS )?

referenced by:

• excelTableSpec

excelTableColumns:

A list of Excel table column specifications.

excelTableColumns
         ::= COLUMNS excelTableColumnSpec ( COMMA excelTableColumnSpec )*

referenced by:

• excelTableSpec

excelTableColumnSpec:

Each Excel column is mapped to a data source column using it's relative position within the rectangular area. Position 1 represents the first Excel column falling within the Excel rectangular area.

excelTableColumnSpec
         ::= identifier dataType POSITION numericConstant

referenced by:

• excelTableColumns

htmlTableSpec:

Data lossy stored in HTML format can be interpreted as a data source using the htmltable keyword.

The expression specifies a master path within the context of which the rows are evaluated using the column specifications.

The passing option specifies the source of the data in HTML format. The source is often the outcome of a read_file() table function or a URL download using httpdownload(). Full adherence to the HTML format is not required; the parser often automatically corrects many HTML format errors.

The columns are specified using their path relative to the master path.

htmlTableSpec
         ::= HTMLTABLE parenthesisOpen htmlTableExpression ( htmlTablePassing | htmlTableLiteral ) htmlTableColumns? parenthesisClose

referenced by:

• dataSource

htmlTableLiteral:

htmlTableLiteral
         ::= LITERAL expression

referenced by:

• htmlTableSpec

htmlTablePassing:

htmlTablePassing
         ::= passingSourceOrPathExpression

referenced by:

• htmlTableSpec

htmlTableExpression:

htmlTableExpression
         ::= stringConstant

referenced by:

• htmlTableSpec

htmlTableColumns:

A list of HTML table column specifications.

htmlTableColumns
         ::= COLUMNS htmlTableColumnSpec ( COMMA htmlTableColumnSpec )*

referenced by:

• htmlTableSpec

htmlTableColumnSpec:

Each HTML table column is mapped to a data source column using the path relative to the master path.

htmlTableColumnSpec
         ::= identifier dataType PATH stringConstant

referenced by:

• htmlTableColumns

stringSplitSpec:

Splits a text upon another text, return one row per element.

Parameters:

• Input: The text to split.
• Splitter: Text being split upon.

Returns: a number of rows with one column named 'value'; one per element in the input.

stringSplitSpec
         ::= STRING_SPLIT parenthesisOpen expression COMMA expression parenthesisClose

referenced by:

• dataSource

internetTableSpec:

Data stored on the Internet accessible through the HTTP protocol can be interpreted as a data source using the internettable keyword. A depth-first scan is done in which solely unique URLs are returned. Starting of at one starting URL, URLs are downloaded from the Internet consisting of webpages and content. Contents is made available with no further deeper inspection. Webpages in HTML format are scanned for more URLs by default for the following paths:

• //a[@href]: all hrefs in anchors;
• //script[@src]: all sources of scripts;
• //link[@href]: all hrefs of links.
• //img[@src]: all hrefs of images.

The startAtExpression specifies the initial webpage to retrieve data for.

A pre-defined list of columns is available per retrieved URL:

• URL: URL of page;
• Contents_char: the character contents, converted from the original character set into UTF-8;
• Contents_blob: the binary contents;
• Mime_type: MIME-type returned by the web server;
• Http_status_code: numeric HTTP response status code;
• Date_retrieval_utc: date/time when the response was received (UTC);
• Retrieval_duration_ms: time between the request and complete response in milliseconds;
• Bytes_retrieved: number of bytes retrieved;
• Depth: recursion depth, starting at 1 for the initial URL;
• Retrieval_successful: indicator whether the response was completely successful retrieved;
• Last_modified: date/time when the response's content was last modified;
• Etag: ETAG on the content as returned by the web server;
• Content_disposition: preferred file name and encoding to be used;
• Cache_Control: contents of cache-control HTTP response header;
• Expires: contents of the Expires HTTP response header;
• Error_message_code: Invantive SQL engine error message code if any occurred;
• Error_message_text: Invantive SQL engine error message code if any occurred.

internetTableSpec
         ::= INTERNETTABLE parenthesisOpen startAtExpression internetTableOptions parenthesisClose

referenced by:

• dataSource

startAtExpression:

startAtExpression
         ::= ( START AT_C )? expression

referenced by:

• internetTableSpec

internetTableOptions:

The process can be controlled using options:

• Stay on site: when present, recursion restricts to URLs on the same host name as the starting URL. Default behaviour is to branch out to other sites too.
• Maximum depth: limit the depth of the recursion to a specific number.
• Ignore errors: do not stop on the first error but continue. Default behaviour is to stop on the first error or result completion, whatever comes first.

internetTableOptions
         ::= ( STAY ON SITE )? ( ( MAX | MAXIMUM ) DEPTH numericConstant )? ( IGNORE ERRORS )?

referenced by:

• internetTableSpec

dataTypeWithLength:

dataTypeWithLength
         ::= dataType ( parenthesisOpen numericConstant ( COMMA numericConstant )? parenthesisClose )?

referenced by:

• castExpression
• createTableArgument

dataType:

List of supported data types. Each data type may be referred to by it's name, but several names will converse to one data type. For instance, 'CHAR' and 'CHARACTER' are synonyms.

dataType ::= BFILE
           | BIGINT
           | BIGSERIAL
           | BIT
           | BLOB
           | BOOL
           | BOOLEAN
           | BPCHAR
           | BYTE
           | BYTEA
           | CHANGES
           | CHAR
           | CHARACTER
           | CLOB
           | DATE
           | DATETIME
           | DATETIMEOFFSET
           | DEC
           | DECIMAL
           | DOUBLE
           | FLOAT
           | FLOAT4
           | FLOAT8
           | GUID
           | IMAGE
           | INT
           | INT16
           | INT2
           | INT32
           | INT4
           | INT64
           | INT8
           | INTEGER
           | INTERVAL
           | LONGBLOB
           | LONGTEXT
           | MEDIUMBLOB
           | MEDIUMINT
           | MEDIUMTEXT
           | MONEY
           | NAME
           | NCHAR
           | NUMBER
           | NUMERIC
           | NVARCHAR
           | OID
           | PLS_INTEGER
           | RAW
           | REAL
           | SERIAL
           | SMALLDATETIME
           | SMALLINT
           | SMALLMONEY
           | SMALLSERIAL
           | TEXT
           | TIME
           | TIMESTAMP
           | TIMESTAMPTZ
           | TIMETZ
           | TINYBLOB
           | TINYINT
           | TINYTEXT
           | UINT16
           | UINT32
           | UINT64
           | UNIQUEIDENTIFIER
           | UUID
           | VARBINARY
           | VARCHAR
           | VARCHAR2
           | XML
           | XMLTYPE
           | YEAR

referenced by:

• csvTableColumnSpec
• dataTypeWithLength
• excelTableColumnSpec
• htmlTableColumnSpec
• jsonTableColumnSpec
• pSqlArgument
• pSqlFunctionSpec
• pSqlItemDeclaration
• xmlTableColumnSpec

groupBy:

Grouping of multiple rows into groups is specified by the groupBy. A group will be introduced for each distinct combination of column values for the columns listed. The values of grouped columns can be used in the select clause. Columns not being grouped upon can only be used within the context of a group function listed as 'aggregateFunction'.

groupBy  ::= GROUP BY columnList

referenced by:

• uniqueSelectStatement

orderBy:

Sort the rows returned as specified by the list of columns. Values are either sorted ascending (the default) or descending.

orderBy  ::= ORDER BY sortedColumnList

referenced by:

• listAggAggregateFunction
• selectStatement

pivotClause:

pivotClause
         ::= PIVOT parenthesisOpen aggregateFunction FOR column IN parenthesisOpen columnNoAliasList parenthesisClose parenthesisClose aliased?

referenced by:

• dataSource

sortDirection:

A sort direction can be either 'asc' for 'ascending' (the default) or 'desc' for 'descending'.

sortDirection
         ::= asc
           | desc

referenced by:

• sortedColumn

columnList:

A comma-separated list of columns.

columnList
         ::= column ( COMMA column )*

referenced by:

• allColumnsSpecId
• groupBy
• identifiedByMultipleClause
• insertFieldList
• setOperatorSelectStatement
• synchronizeStatement

sortedColumnList:

An ordered comma-separated list of column values to sort upon.

sortedColumnList
         ::= sortedColumn ( COMMA sortedColumn )*

referenced by:

• orderBy
• resolveByClause

sortedColumn:

A column values to sort upon.

sortedColumn
         ::= column sortDirection?

referenced by:

• sortedColumnList

column:

A column is identified by an identifier, possibly prefixed by the name of the table or the alias of the table from which the column is to be taken.

column   ::= identifier ( DOT identifier )?

referenced by:

• columnList
• pivotClause
• sortedColumn
• updateValue

columnNoAliasList:

columnNoAliasList
         ::= columnNoAlias ( COMMA columnNoAlias )*

referenced by:

• pivotClause

columnNoAlias:

columnNoAlias
         ::= identifier

referenced by:

• columnNoAliasList

whereClause:

The where-clause restricts the number of rows in a result set by applying one or more boolean condiditions which rows must satisfy.

whereClause
         ::= WHERE booleanExpression

referenced by:

• deleteStatement
• uniqueSelectStatement
• updateStatement

joinStatements:

A list of join statements.

joinStatements
         ::= joinStatement+

referenced by:

• uniqueSelectStatement

joinStatement:

A join statement combines two result sets. Only combinations of rows taken from both result sets are returned when they meet the join conditions.

joinStatement
         ::= joinCategory join dataSource joinConditions?

referenced by:

• joinStatements

joinCategory:

The join category specifies what combinations of rows are considered. The following variants can be used:

• inner join, as indicated by 'join' or 'inner join': an inner join returns all combinations of rows from both result sets that meet the join conditions.
• left outer, as indicated by 'left outer join': a left outer join returns the same rows as an inner join, extended by one row for each row in the left result set having no matching rows in the right result set. Each column that originates from the right result set is assigned a null value.
• right outer, as indicated by 'right outer join': a right outer join returns the same rows as an inner join, extended by one row for each row in the right result set having no matching rows in the left result set. Each column that originates from the left result set is assigned a null value.
• full outer, as indicated by 'full outer join': a full outer join returns the same rows as an inner join, extended by one row for each row in the right result set having no matching rows in the left result set. Each column that originates from the left result set is assigned a null value. The results are also extended by one row for each row in the left result set having no matching rows in the right result set. Each column that originates from the right result set is assigned a null value.
• cross join, as indicated by 'cross join': a cross join returns a Cartesian product of the rows from both result sets. A 'Cartesian product' is a term from set theory, which indicates that all combinations are returned.

joinCategory
         ::= ( inner | joinSubCategory outer? | cross )?

referenced by:

• joinStatement

joinSubCategory:

The join sub-category refines the join category. Please see 'joinCategory' for an explanation.

joinSubCategory
         ::= left
           | right
           | full

referenced by:

• joinCategory

join:

join     ::= JOIN

referenced by:

• joinStatement

inner:

inner    ::= INNER

referenced by:

• joinCategory

outer:

outer    ::= OUTER

referenced by:

• joinCategory

left:

Extracts a substring from a value with the given length from the left side. Keyword is also used with joins.
Parameters:

• Input: Text to extract substring from.
• Length: Maximum length of the substring.

left     ::= LEFT

referenced by:

• functionExpression
• joinSubCategory

right:

Extracts a substring from a value with the given length from the right side. Keyword is also used with joins.
Parameters:

• Input: Text to extract substring from.
• Length: Maximum length of the substring.

right    ::= RIGHT

referenced by:

• functionExpression
• joinSubCategory

full:

full     ::= FULL

referenced by:

• joinSubCategory

cross:

cross    ::= CROSS

referenced by:

• joinCategory

sum:

Group function to sum together individual numerical values. Occurrences of null are considered 0, unless there are only null values. In that case the outcome is null.

sum      ::= SUM

referenced by:

• sumAggregateFunction

product:

Group function to multiply together individual numerical values. Multiplying large values can quickly exceed the range of the resulting Decimal data type. The product group function is typically used in financial and probability calculations with values near 1.

product  ::= PRODUCT

referenced by:

• productAggregateFunction

min:

Group function to find the minimum value from a group of numerical values or text values.

min      ::= MIN

referenced by:

• minAggregateFunction

max:

Group function to find the maximum value from a group of numerical values or text values.

max      ::= MAX

referenced by:

• maxAggregateFunction

avg:

Group function to find the average value from a group of numerical values.

avg      ::= AVG

referenced by:

• avgAggregateFunction

first:

Group function to the first non-null value in an ordered result set.

first    ::= FIRST

referenced by:

• firstAggregateFunction

last:

Group function to the last non-null value in an ordered result set.

last     ::= LAST

referenced by:

• lastAggregateFunction

stddev:

Group function to find the standard deviation from a group of numerical values.

stddev   ::= STDDEV

referenced by:

• stdDevAggregateFunction

count:

Group function to find the number of values from a group of values.

count    ::= COUNT

referenced by:

• countAggregateFunction

listagg:

Group function which concatenates all individual values, separated by the separator when provided and comma plus space otherwise.

listagg  ::= LISTAGG

referenced by:

• listAggAggregateFunction

asc:

asc      ::= ASC

referenced by:

• sortDirection

desc:

desc     ::= DESC

referenced by:

• sortDirection

joinConditions:

A boolean expression which defines valid combinations of the join.

joinConditions
         ::= ON booleanExpression

referenced by:

• joinStatement

selectList:

selectList
         ::= selectPart ( COMMA selectPart )*

referenced by:

• uniqueSelectStatement

selectPart:

selectPart
         ::= part aliased? labeled?

referenced by:

• selectList

aliased:

aliased  ::= AS? alias

referenced by:

• dataSource
• pivotClause
• selectPart

labeled:

Defines the textual label of an expression. The label may contain resources in the format '{res:resource code}' such as 'My {res:itgen_description}'. Similar to the data type and alias of an expression, the label is maintained across selections. Application of a calculation or a SQL function resets the label to the empty value. User interfaces can choose to display the label when available instead of the column name to provide a more natural interface.

labeled  ::= LABEL stringConstant

referenced by:

• selectPart

part:

part     ::= expression
           | aggregateFunction
           | allColumnsSpec

referenced by:

• countAggregateFunction
• selectPart

aggregateFunction:

aggregateFunction
         ::= sumAggregateFunction
           | productAggregateFunction
           | minAggregateFunction
           | maxAggregateFunction
           | firstAggregateFunction
           | lastAggregateFunction
           | avgAggregateFunction
           | stdDevAggregateFunction
           | listAggAggregateFunction
           | countAggregateFunction
           | zipAggregateFunction

referenced by:

• part
• pivotClause

sumAggregateFunction:

sumAggregateFunction
         ::= sum parenthesisOpen distinct? arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

productAggregateFunction:

productAggregateFunction
         ::= product parenthesisOpen distinct? arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

minAggregateFunction:

minAggregateFunction
         ::= min parenthesisOpen arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

maxAggregateFunction:

maxAggregateFunction
         ::= max parenthesisOpen arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

firstAggregateFunction:

firstAggregateFunction
         ::= first parenthesisOpen arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

lastAggregateFunction:

lastAggregateFunction
         ::= last parenthesisOpen arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

avgAggregateFunction:

avgAggregateFunction
         ::= avg parenthesisOpen distinct? arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

stdDevAggregateFunction:

stdDevAggregateFunction
         ::= stddev parenthesisOpen distinct? arithmeticExpression parenthesisClose

referenced by:

• aggregateFunction

listAggAggregateFunction:

listAggAggregateFunction
         ::= listagg parenthesisOpen distinct? arithmeticExpressionList parenthesisClose ( WITHIN GROUP parenthesisOpen orderBy parenthesisClose )?

referenced by:

• aggregateFunction

countAggregateFunction:

countAggregateFunction
         ::= count parenthesisOpen distinct? part parenthesisClose

referenced by:

• aggregateFunction

zipAggregateFunction:

zipAggregateFunction
         ::= zip parenthesisOpen arithmeticExpressionList parenthesisClose

referenced by:

• aggregateFunction

allColumnsSpec:

Selects all columns from an SQL statement or from one or more data sources used. The except clause allows retrieval of all columns except the listed names. The except clause is typically used with wide result sets with possibly varying column lists from which a few pre-defined columns need to excluded.

allColumnsSpec
         ::= allColumnsSpecId allColumnsSpecColumnNamePrefix? allColumnsSpecColumnNamePostfix? allColumnsSpecLabelPrefix? allColumnsSpecLabelPostfix?

referenced by:

• part

allColumnsSpecId:

allColumnsSpecId
         ::= ( alias DOT )? ASTERIX ( EXCEPT columnList )?

referenced by:

• allColumnsSpec

allColumnsSpecColumnNamePrefix:

Defines the aliases of columns selected by the select all ('*') operator as their original alias, prefixed with the specified text. For instance, the select operator changes the alias of the column 'code' with the prefix 'pjt_' into 'pjt_code'.

allColumnsSpecColumnNamePrefix
         ::= PREFIX WITH stringConstant

referenced by:

• allColumnsSpec

allColumnsSpecColumnNamePostfix:

Defines the aliases of columns selected by the select all ('*') operator as their original alias, postfixed with the specified text. For instance, the select operator changes the alias of the column 'code' with the postfix '_from_table2' into 'code_from_table2'.

allColumnsSpecColumnNamePostfix
         ::= POSTFIX WITH stringConstant

referenced by:

• allColumnsSpec

allColumnsSpecLabelPrefix:

Defines the label of columns selected by the select all ('*') operator as their original label, prefixed with the specified text. For instance, the select operator changes the label 'Name' of the column 'name' with the prefix 'Project ' into 'Project Name'.

allColumnsSpecLabelPrefix
         ::= LABEL PREFIX WITH stringConstant

referenced by:

• allColumnsSpec

allColumnsSpecLabelPostfix:

Defines the label of columns selected by the select all ('*') operator as their original label, postfixed with the specified text. For instance, the select operator changes the label 'Name' of the column 'name' with the postfix ' from Table2' into 'Name from Table2'.

allColumnsSpecLabelPostfix
         ::= LABEL POSTFIX WITH stringConstant

referenced by:

• allColumnsSpec

ddlStatement:

All available Data Definition Language statements.

ddlStatement
         ::= createTableStatement
           | dropTableStatement
           | alterPersistentCacheStatement
           | alterDataDictionaryStatement
           | alterSessionStatement

referenced by:

• sqlStatement

alterPersistentCacheStatement:

Besides an in-memory cache valid during the duration of a session, Invantive SQL offers an integrated cache storing data persistently using an on-premise or cloud relation database such as SQL Server or PostgreSQL. When configured, Invantive SQL first tries to find sufficiently fresh data in the cache. This reduces the number of data loads from slow data containers such as some cloud platforms. In general, the performance increase when the rows can be fully retrieved from a cache is between a factor 25 and 2.500.

Invantive SQL itself manages the table structure and table contents in the relation database used as a data cache. On initial use just provide an empty database. Platforms supported include SQL Server, Oracle, PostgreSQL and MySQL. Invantive SQL installs a repository consisting of a dozen tables. The repository tables have names starting with 'dc_'. Most repository tables are accompanied by a view named identically except for a trailing '_r'. The repository views join in all master tables for easier analysis from within the database storing the facts

For each table partition version, a so-called 'facts table' is created. A facts table contains a full copy of the rows retrieved from the data container. Facts tables have names starting with 'dcd_', followed by a unique hash signaling the table partition version. When necessary, additional database objects are maintained such as indexes to improve performance. As with facts table names, all column names are also hashed based upon an algorithm including the original column name. These facts tables are not intended for direct use using native SQL.

Each facts table has a unique state from the following state, with 'Ready' state signaling the now current version:

• Initializing ('I'): the facts table will be created.
• View creation ('V'): logical views will be created.
• Prepared ('P'): the facts table has been created, but contains yet no rows.
• Seeding ('S'): the facts table is being seeded with the contents of the previously current version.
• Loading ('L'): loading new facts from data container using water shed or another algorithm.
• Ready ('R'): the facts table is available and the current one to be used.
• Obsoleted ('O'): the facts table still exists, but the data has passed it's conservation period. Often a newer version is now current.
• Dropped ('D'): the facts table now longer exist, but the metadata is still present in the repository tables.

The persistent cache in the database can be used with native SQL when extended by Invantive Data Replicator. Invantive Data Replicator creates and maintains database view (a so-called 'partition view') for the now current version of table partition. Similarly, it can create an 'overall view', showing the rows across all partitions of the now current versions per partition. Invantive Data Replicator provides a high-performance high-volume operational data store with little effort.

The overall views are typically used for consolidation purposes, bringing together data across multiple companies or persons.

alterPersistentCacheStatement
         ::= alterPersistentCacheSetStatement
           | alterPersistentCacheDownloadStatement
           | alterPersistentCachePurgeStatement
           | alterPersistentCacheRefreshStatement
           | alterPersistentCacheLoadStatement
           | alterPersistentCacheTableRefreshStatement
           | alterPersistentCachePartitionRefreshStatement
           | alterPersistentCacheDropStatement
           | alterPersistentCacheConfigureWebhooksStatement

referenced by:

• ddlStatement

alterPersistentCachePurgeStatement:

The purge statement maintains existing table partition versions. It can purge table partition versions in several ways:

• ready: selects table partition versions in state 'Ready'. Table partition versions go to the state 'Obsolete'. Facts are not deleted.
• obsolete: selects table partition versions in state 'Obsolete'. Facts of these are deleted. Table partition versions go to the state 'Dropped'. The facts deleted might be used in queries that started running when they were in state 'Ready'. The facts can be deleted safely when the database platform supports reading data from rows already deleted, such as Oracle. Purging obsolete facts is often used as a background task separate from refresh to reduce refresh runtime, since the deletion of data has already been executed before refresh.
• droppable: selects table partition versions in state 'Dropped'. Facts are deleted when any facts are still present despite the dropped state. This will be useful only under exceptional conditions, since the dropped status indicates that the facts have already been deleted. Table partition versions remain in the state 'Dropped'.
• unknown: queries the database for tables that match the naming convention 'dcd...' or 'dcs...'. Any tables that is not registered in the repository is dropped from the database. This will be useful only under exceptional conditions, such as when the repository was unintentionally dropped or incorrectly changed.
• all: first performs the "unknown" variant and then the "droppable" variant. This will be useful only under exceptional conditions.

Specification of a data container limits the scope of table partition versions to the listed data container.

alterPersistentCachePurgeStatement
         ::= ALTER PERSISTENT CACHE PURGE ( UNKNOWN | DROPPABLE | OBSOLETE | READY | ALL ) TABLE PARTITION VERSIONS ( DATACONTAINER stringConstant )?

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheDownloadStatement:

alterPersistentCacheDownloadStatement
         ::= ALTER PERSISTENT CACHE DOWNLOAD FEED ( LICENSE CONTRACT CODE stringConstant )? ( TOKEN stringConstant )? ( DATACONTAINER stringConstant )? ( PARTITION partitionSimpleIdentifier )? ( LIMIT numericConstant )? ( NO DELETE )?

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheConfigureWebhooksStatement:

Trickle loading is an advanced strategy to efficiently and fast update the replicate data. It requires all changes (insert, update and delete) on the replicated data to be registered as event messages, possibly with a delay.

The registration of changes can be activated on selected platforms using webhooks. Configuration of the webhooks can be done using this statement. When not specified, the license contract code of the current subscription will be used.

alterPersistentCacheConfigureWebhooksStatement
         ::= ALTER PERSISTENT CACHE ( ENABLE | DISABLE ) WEBHOOKS ( LICENSE CONTRACT CODE stringConstant )? ( TABLE tableSpec )? ( PARTITION partitionSimpleIdentifier )? ( DATACONTAINER stringConstant )?

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheRefreshStatement:

This statement triggers the refresh of replicated data of all connected or one specific data container alias. A refresh of the replicated data can also be triggered by executing a SQL statement specifying the use of replicated data of a specific age.

In default mode (without 'force'), a refresh is only executed on the specified data when the age of the replicated data exceeds the configured maximum age. An offset to the configured maximum age can be specified using the 'when obsolete within' clause. With a forced refresh, the replicated data is always replaced by a recent version of the source data.

The maximum number of parallel processes to replicate the data can be configured using the 'parallel' clause to increase throughput and decrease runtime of the replication process.

By default the approach as configured on each table partition is used to update the replica. However, a deviating approach can be specified. This is typically done after switching a table partition to trickle loading to run a one-time replication process with the copy approach, effectively ensuring that no changes have gone unnoticed.

alterPersistentCacheRefreshStatement
         ::= ALTER PERSISTENT CACHE FORCE? REFRESH ( DATACONTAINER dataContainerAlias? )? ( PARALLEL numericConstant )? ( APPROACH ( COPY | TRICKLE | SAMPLE | DEFAULT ) )? ( WHEN OBSOLETE WITHIN intervalConstant )?

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheLoadStatement:

Loads all available tables across all connected data containers in the cache. Typically used for demonstrations.

alterPersistentCacheLoadStatement
         ::= ALTER PERSISTENT CACHE LOAD

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheTableRefreshStatement:

Refresh all data of a specificied table. The options are explained at alterPersistentCacheRefreshStatement.

alterPersistentCacheTableRefreshStatement
         ::= ALTER PERSISTENT CACHE TABLE tableSpec FORCE? REFRESH ( PARTITION partitionIdentifierWithAlias )? ( PARALLEL numericConstant )? ( APPROACH ( COPY | TRICKLE | SAMPLE | DEFAULT ) )? ( WHEN OBSOLETE WITHIN intervalConstant )?

referenced by:

• alterPersistentCacheStatement

alterPersistentCachePartitionRefreshStatement:

Refresh all data of a specificied partition. The options are explained at alterPersistentCacheRefreshStatement.

alterPersistentCachePartitionRefreshStatement
         ::= ALTER PERSISTENT CACHE PARTITION partitionIdentifierWithAlias FORCE? REFRESH ( PARALLEL numericConstant )? ( APPROACH ( COPY | TRICKLE | SAMPLE | DEFAULT ) )? ( WHEN OBSOLETE WITHIN intervalConstant )?

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheDropStatement:

Drops all facts stored in table partition versions from the database and the associated metadata from the cache repository for a range of table partitions. The tables and partitions can be specified using the table, partition and/or data container clause.

alterPersistentCacheDropStatement
         ::= ALTER PERSISTENT CACHE DROP ( TABLE tableSpec ( PARTITION partitionIdentifier )? | PARTITION partitionIdentifier | DATACONTAINER stringConstant )

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheSetStatement:

Change central maintained settings for the cache, including defaults for new tables or data containers.

• Fresh: number of seconds during which facts are considered fresh after completiong of loading.
• Retention: number of seconds trickle loading messages are kept available after processing.
• Metadata: number of seconds metadata are kept available after dropping the associated facts in the version.
• Metadata recyclebin: number of seconds dropped metadata are kept available.
• Version: reset data model version to another version to re-run upgrade scripts.
• Token: secret text used as token to retrieve trickle loading messages.
• Prefix/postfix: prefix and/or postfix for Invantive Data Replicator maintained database views.
• Maintain: indicator whether to maintain database views per table partition and/or overall table.
• My Messages: indicator whether to place a copy of incoming trickle loading messages in dc_my_incoming_messages for custom code purposes.
• Index Tables: whether to create non-unique indexes on the facts tables for speedier retrieval on database views.
• Auto Upgrade: whether to run a data model upgrade once.
• Data Container Prefix/postfix: prefix and/or postfix for Invantive Data Replicator maintained overall views.

alterPersistentCacheSetStatement
         ::= ALTER PERSISTENT CACHE SET ( ( FRESH | RETENTION FORWARDED INCOMING MESSAGES | METADATA? RECYCLEBIN | DATA MODEL VERSION ) numericConstant | TOKEN stringConstant | LOGICAL ( OVERALL | PARTITION ) VIEW ( NAME ( PREFIX | POSTFIX ) stringConstant | MAINTAIN booleanConstant ) | ( LOAD MY MESSAGES | INDEX ( FACTS | HISTORY ) TABLES ) booleanConstant | AUTO UPGRADE ONCE | alterPersistentCacheSetBackingConnectionString | alterPersistentCacheSetTableOptions | alterPersistentCacheSetDataContainerOptions )

referenced by:

• alterPersistentCacheStatement

alterPersistentCacheSetBackingConnectionString:

alterPersistentCacheSetBackingConnectionString
         ::= BACKING ENCRYPTED CONNECTION STRING stringConstant

referenced by:

• alterPersistentCacheSetStatement

alterPersistentCacheSetDataContainerOptions:

Changes settings of a data container.

The prefix and postfix for the logical overall view of every new replicated table registered in the data container can be specified. This is typically done when there are multiple data containers pointing to the same platform, such as multiple subscriptions on Teamleader or multiple Exact Online countries. Specifying a prefix ensures that the automatically generated logical overall views have unique and recognizable names.

alterPersistentCacheSetDataContainerOptions
         ::= DATACONTAINER stringConstant LOGICAL OVERALL VIEW NAME ( PREFIX | POSTFIX ) stringConstant

referenced by:

• alterPersistentCacheSetStatement

alterPersistentCacheSetTableOptions:

Change table and table partition-specific settings for the cache. Most options are identical as on alterPersistentCacheSetStatement, but intended for table/table partition-specific deviations. Additional options are:

• State: move all table partition versions with an earlier state to a specific end state.
• Approach: replication strategy.

alterPersistentCacheSetTableOptions
         ::= TABLE tableSpec ( LOGICAL ( OVERALL VIEW ( MAINTAIN booleanConstant | NAME stringConstant ) | PARTITION VIEW ( MAINTAIN booleanConstant | NAME ( PREFIX | POSTFIX ) stringConstant ) ) | INDEX ( FACTS | HISTORY ) TABLE booleanConstant | STATE ( OBSOLETE | DROPPED ) | ( PARTITION partitionIdentifier )? APPROACH ( COPY | TRICKLE | SAMPLE ) ( LICENSE CONTRACT CODE stringConstant )? )

referenced by:

• alterPersistentCacheSetStatement

alterDataDictionaryStatement:

alterDataDictionaryStatement
         ::= alterDataDictionarySetStatement

referenced by:

• ddlStatement

alterSessionStatement:

Various statement to alter properties of the current Invantive SQL session.

alterSessionStatement
         ::= alterSessionSetStatement

referenced by:

• ddlStatement

alterSessionSetStatement:

Change properties of the current Invantive SQL session.

alterSessionSetStatement
         ::= ALTER SESSION SET ( alterSessionSetBillingId | alterSessionSetBillingReference | alterSessionSetIuidSourceAlias | alterSessionSetRoles )

referenced by:

• alterSessionStatement

alterDataDictionarySetStatement:

Associate a persistent store to back the data dictionary associated with the current Invantive SQL session. The persistent store will be used to store and retrieve object definitions such as PSQL procedures, SQL views and PSQL packages.

alterDataDictionarySetStatement
         ::= ALTER DATA DICTIONARY SET alterDataDictionarySetBackingConnectionString

referenced by:

• alterDataDictionaryStatement

alterDataDictionarySetBackingConnectionString:

alterDataDictionarySetBackingConnectionString
         ::= BACKING ENCRYPTED CONNECTION STRING stringConstant

referenced by:

• alterDataDictionarySetStatement

alterSessionSetBillingId:

Change the ID used for to charge usage to another agreement as specified in the license. Used solely for hosted use of Invantive products to associate usage statistics on data downloaded, data uploaded, users, devices and partitions use with individual billing IDs. Original agreement code is also registered.

alterSessionSetBillingId
         ::= BILLING ID expression

referenced by:

• alterSessionSetStatement

alterSessionSetBillingReference:

Add a reference to billing to differentiate use within one agreement depending on application. department or operating company. Typically used for use of Invantive products by larger organizations to associate usage statistics on data downloaded, data uploaded, users, devices and partitions use with individual billing references.

alterSessionSetBillingReference
         ::= BILLING REFERENCE expression

referenced by:

• alterSessionSetStatement

alterSessionSetIuidSourceAlias:

Use a non-default data container to determine the IUID (Invantive User ID) value used for identifying the user. Typically used when the actual user is available in a user directory of a higher-numbered data container.

alterSessionSetIuidSourceAlias
         ::= IUID SOURCE expression

referenced by:

• alterSessionSetStatement

alterSessionSetRoles:

Invantive SQL offers advanced security features including row-level security with custom connectors. Sets the active roles of a session.

alterSessionSetRoles
         ::= ROLES ( DEFAULT | roleIdentifier ( COMMA roleIdentifier )* )

referenced by:

• alterSessionSetStatement

createTableStatement:

Create a table on the specified platform, filled with data from the select statement. An error is raised when a table with the same name already exists. When 'or replace' is specified, a previously existing table with the same name is dropped before creating the new table.

A new table is assigned a technical primary key column. Also indexes are created by default where deemed useful.

createTableStatement
         ::= CREATE orReplace? TABLE tableSpec ( AS selectStatement | parenthesisOpen createTableArgument ( COMMA createTableArgument )* parenthesisClose ) ( BATCHSIZE numericConstant )?

referenced by:

• ddlStatement

createTableArgument:

createTableArgument
         ::= identifier dataTypeWithLength ( NOT? NULL )?

referenced by:

• createTableStatement

dropTableStatement:

Drop the specified table on the specified platform. An error is raised when no table exists by that name.

dropTableStatement
         ::= DROP TABLE tableSpec

referenced by:

• ddlStatement

orReplace:

orReplace
         ::= OR REPLACE

referenced by:

• createTableStatement

setStatement:

Replaces the value of a provider attribute by a new value.

setStatement
         ::= SET setIdentifier expression

referenced by:

• sqlStatement

setIdentifier:

setIdentifier
         ::= attributeIdentifier distributedAliasDirective?

referenced by:

• setStatement

transactionStatement:

transactionStatement
         ::= beginTransactionStatement
           | rollbackTransactionStatement
           | commitTransactionStatement

referenced by:

• sqlStatement

executeFileStatement:

Execute a file loaded from the operating system with Invantive SQL statements. The file may contain Invantive SQL and Procedural SQL. The use of Invantive Script is not possible; Invantive Script is a client-side solution such as with Invantive Data Hub and Invantive Query Tool.

executeFileStatement
         ::= FILE_PATH

referenced by:

• sqlStatement

beginTransactionStatement:

A begin transaction statement initiates a transaction. Invantive SQL typically provides no transaction logic given the distributed nature and the limitations of the possible platforms. Some platforms enable collection of transaction data, which are to be handed over to the backing platform all together.

beginTransactionStatement
         ::= BEGIN TRANSACTION? ( DATACONTAINER stringConstant )?

referenced by:

• transactionStatement

rollbackTransactionStatement:

Forgets all collected transaction data not yet handed over to the backing platform.

rollbackTransactionStatement
         ::= ROLLBACK TRANSACTION? ( DATACONTAINER stringConstant )?

referenced by:

• transactionStatement

commitTransactionStatement:

Hand over all collected transaction to the backing platform for registration.

commitTransactionStatement
         ::= COMMIT TRANSACTION? ( DATACONTAINER stringConstant )?

referenced by:

• transactionStatement

useStatement:

The use statement enables you to specify which partitions should be accessed by subsequent select, insert, update and delete statements. You can specify one or multiple partitions as a comma-separated list, possibly for a specific data container by appending an at-sign plus data container alias to the partition code. The value 'default' has a special meaning; it specifies to use the partition(s) originally selected when you logged on. The value 'all' also has a special meaning: it selects all partitions available.

For instance, to select partition '35' in the data container with alias 'eolnl' and partition '57345' in the data container with alias 'nmbrsnl', you can execute: 'use 35@eolnl, 57345@nmbrsnl'.

For complex scenarios, you can specify any valid Invantive SQL select statement which returns one or two columns. Each row from the query specifies one partition to select. The first column specifies the partition code, whereas the optional second column specifies a specific data container alias.

For instance, to select partition '35' in the data container with alias 'eolnl' and partition '57345' in the data container with alias 'nmbrsnl', you can execute: 'use select '35', 'eolnl' from dual@datadictionary union all select '57345', 'nmbrsnl' from dual@datadictionary'.

useStatement
         ::= USE ( partitionIdentifiersList | selectStatement )

referenced by:

• sqlStatement

partitionIdentifiersList:

partitionIdentifiersList
         ::= partitionIdentifierWithAlias ( COMMA partitionIdentifierWithAlias )*

referenced by:

• useStatement

partitionIdentifier:

A partition identifier uniquely identifies one or more partitions from the currently available data containers.

The special partition identifer 'default' stands for all partitions that were chosen as default partitions during setup of the database connection. The special partition identifier 'all' stands for all available partitions across all available database connections.

partitionIdentifier
         ::= parameterExpression
           | stringConstant
           | numericConstant
           | identifier
           | ALL
           | DEFAULT

referenced by:

• alterPersistentCacheDropStatement
• alterPersistentCacheSetTableOptions
• partitionIdentifierWithAlias

partitionIdentifierWithAlias:

partitionIdentifierWithAlias
         ::= partitionIdentifier distributedAliasDirective?

referenced by:

• alterPersistentCachePartitionRefreshStatement
• alterPersistentCacheTableRefreshStatement
• partitionIdentifiersList