Grammar

•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 UniversalSQL 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.

uniqueSelectStatement setOperatorSelectStatement orderBy limitClause

selectStatement
::= uniqueSelectStatement setOperatorSelectStatement* orderBy? limitClause?

referenced by:

•createTableStatement

•embeddedSelect

•inSelectStatement

•pSqlForRecordLoopStatement

•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'.

selectStatement

inSelectStatement
::= selectStatement

referenced by:

setOperatorSelectStatement:

SQL is based upon a solid mathematical foundation named 'set theory' with some exceptions. The set operators of Invantive UniversalSQL 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.

UNION ALL MINUS_C INTERSECT uniqueSelectStatement

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

referenced by:

uniqueSelectStatement:

Retrieves a data set from one or more data containers.

select executionHints distinct topClause selectList INTO variableList FROM dataSource joinStatements whereClause groupBy

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

referenced by:

•setOperatorSelectStatement

dataSource:

A data source can be a table, a table with parameters or a nested select (an 'inline view').

tableOrFunctionSpec embeddedSelect xmlTableSpec csvTableSpec jsonTableSpec aliased

dataSource
::= ( tableOrFunctionSpec | embeddedSelect | xmlTableSpec | csvTableSpec | jsonTableSpec ) aliased?

referenced by:

select:

SELECT

select ::= SELECT

referenced by:

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 UniversalSQL requires a hint to be valid according to the grammar when specified. This reduces the engineering risk that hints become invalid by accident.

EXECUTION_HINT_START joinSet noJoinSet ods resultSetName lowCost httpDiskCache httpMemoryCache EXECUTION_HINT_END

referenced by:

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 UniversalSQL sessions.

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

HTTP_DISK_CACHE PARENTHESIS_OPEN booleanConstant COMMA booleanConstant COMMA intervalConstant PARENTHESIS_CLOSE

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

referenced by:

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 UniversalSQL sessions.

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

HTTP_MEMORY_CACHE PARENTHESIS_OPEN booleanConstant COMMA booleanConstant COMMA intervalConstant PARENTHESIS_CLOSE

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

referenced by:

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.

ODS PARENTHESIS_OPEN booleanConstant COMMA intervalConstant PARENTHESIS_CLOSE

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

referenced by:

resultSetName:

RESULT_SET_NAME PARENTHESIS_OPEN stringConstant PARENTHESIS_CLOSE

resultSetName
::= RESULT_SET_NAME ( PARENTHESIS_OPEN stringConstant PARENTHESIS_CLOSE )?

referenced by:

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.

select /*+ join_set(sil, invoiceid, 10000) */ * from ExactOnlineREST..SalesInvoices sik join ExactOnlineREST..SalesInvoiceLines sil on sil.invoiceid = sik.invoiceid where sik.status = 50 and sik.InvoiceDate between to_date( :P_RECEIPT_DATE_FROM, 'yyyymmdd') and to_date( :P_RECEIPT_DATE_TO, 'yyyymmdd')

JOIN_SET PARENTHESIS_OPEN identifier COMMA identifier COMMA numericConstant PARENTHESIS_CLOSE

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

referenced by:

noJoinSet:

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

NO_JOIN_SET PARENTHESIS_OPEN identifier COMMA identifier PARENTHESIS_CLOSE

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

referenced by:

variableList:

variableName COMMA variableName

variableList
::= variableName ( COMMA variableName )?

referenced by:

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.

LOW_COST

lowCost ::= LOW_COST

referenced by:

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 ::= DISTINCT

referenced by:

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'.

TOP numericConstant

topClause
::= TOP numericConstant

referenced by:

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'.

LIMIT numericConstant

limitClause
::= LIMIT numericConstant

referenced by:

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 UniversalSQL 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.

parenthesisOpen selectStatement parenthesisClose

embeddedSelect
::= parenthesisOpen selectStatement parenthesisClose

referenced by:

•alterPersistentCacheDropStatement

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.

fullTableIdentifier distributedAliasDirective

tableSpec
::= fullTableIdentifier distributedAliasDirective?

referenced by:

•alterPersistentCacheTableRefreshStatement

•createTableStatement

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.

fullTableIdentifier tableFunctionSpec distributedAliasDirective

tableOrFunctionSpec
::= fullTableIdentifier tableFunctionSpec? distributedAliasDirective?

referenced by:

•partitionIdentifierWithAlias

tableFunctionSpec:

A comma-separated list of parametres to determine the rows to be retrieved by a tableOrFunctionSpec.

parenthesisOpen expression COMMA parenthesisClose

tableFunctionSpec
::= parenthesisOpen ( expression ( COMMA expression )* )? parenthesisClose

referenced by:

•tableOrFunctionSpec

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.

AT dataContainerAlias

distributedAliasDirective
::= AT dataContainerAlias

referenced by:

•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 UniversalSQL to precisely determine to what data container forward a request for data.

identifier

dataContainerAlias
::= identifier

referenced by:

•alterPersistentCacheRefreshStatement

•distributedAliasDirective

xmlTableSpec:

XMLTABLE parenthesisOpen stringConstant null xmlTablePassing xmlTableLiteral xmlTableColumns parenthesisClose

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

referenced by:

xmlTablePassing:

PASSING expression

xmlTablePassing
::= PASSING expression

referenced by:

xmlTableLiteral:

LITERAL expression

xmlTableLiteral
::= LITERAL expression

referenced by:

xmlTableColumns:

COLUMNS xmlTableColumSpec COMMA

xmlTableColumns
::= COLUMNS xmlTableColumSpec ( COMMA xmlTableColumSpec )*

referenced by:

xmlTableColumSpec:

identifier dataType PATH stringConstant

xmlTableColumSpec
::= identifier dataType PATH stringConstant

referenced by:

•xmlTableColumns

jsonTableSpec:

JSONTABLE parenthesisOpen stringConstant null jsonTablePassing jsonTableLiteral jsonTableColumns parenthesisClose

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

referenced by:

jsonTablePassing:

PASSING expression

jsonTablePassing
::= PASSING expression

referenced by:

jsonTableLiteral:

LITERAL expression

jsonTableLiteral
::= LITERAL expression

referenced by:

jsonTableColumns:

COLUMNS jsonTableColumSpec COMMA

jsonTableColumns
::= COLUMNS jsonTableColumSpec ( COMMA jsonTableColumSpec )*

referenced by:

jsonTableColumSpec:

identifier dataType PATH stringConstant

jsonTableColumSpec
::= identifier dataType PATH stringConstant

referenced by:

•jsonTableColumns

csvTableSpec:

CSVTABLE parenthesisOpen csvTablePassing csvTableLiteral csvTableOptions csvTableColumns parenthesisClose

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

referenced by:

csvTableOptions:

ROW DELIMITER stringConstant COLUMN DELIMITER stringConstant SKIP_ LINES numericConstant

csvTableOptions
::= ( ROW DELIMITER stringConstant )? ( COLUMN DELIMITER stringConstant )? ( SKIP_ LINES numericConstant )?

referenced by:

csvTableLiteral:

LITERAL expression

csvTableLiteral
::= LITERAL expression

referenced by:

csvTablePassing:

PASSING expression

csvTablePassing
::= PASSING expression

referenced by:

csvTableColumns:

COLUMNS csvTableColumSpec COMMA

csvTableColumns
::= COLUMNS csvTableColumSpec ( COMMA csvTableColumSpec )*

referenced by:

csvTableColumSpec:

identifier dataType POSITION numericConstant

csvTableColumSpec
::= identifier dataType POSITION numericConstant

referenced by:

•csvTableColumns

dataType:

BFILE BIGINT BIGSERIAL BIT BLOB BOOL BOOLEAN BPCHAR BYTE BYTEA 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 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:

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'.

GROUP BY columnList

groupBy ::= GROUP BY columnList

referenced by:

orderBy:

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

ORDER BY column sortDirection COMMA

orderBy ::= ORDER BY column sortDirection? ( COMMA column sortDirection? )*

referenced by:

sortDirection:

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

asc desc

sortDirection
::= asc
| desc

referenced by:

•orderBy

columnList:

A comma-separated list of columns.

column COMMA

columnList
::= column ( COMMA column )*

referenced by:

•groupBy

•insertFieldList

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.

identifier DOT identifier

column ::= identifier ( DOT identifier )?

referenced by:

•columnList

•orderBy

•updateValue

whereClause:

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

WHERE booleanExpression

whereClause
::= WHERE booleanExpression

referenced by:

•deleteStatement

•updateStatement

joinStatements:

A list of join statement.

joinStatement

joinStatements
::= joinStatement+

referenced by:

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.

joinCategory join dataSource joinConditions

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.

inner joinSubCategory outer cross

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

referenced by:

joinSubCategory:

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

left right full

joinSubCategory
::= left
| right
| full

referenced by:

join:

JOIN

join ::= JOIN

referenced by:

inner:

INNER

inner ::= INNER

referenced by:

outer:

OUTER

outer ::= OUTER

referenced by:

left:

LEFT

left ::= LEFT

referenced by:

•joinSubCategory

right:

Extracts a substring from a value with the given length from the right side.

Parameters:

•Input: Text to extract substring from.

•Length: Maximum length of the substring.

Returns: Substring from the right side of the input. RIGHT

right ::= RIGHT

referenced by:

•joinSubCategory

full:

FULL

full ::= FULL

referenced by:

•joinSubCategory

cross:

CROSS

cross ::= CROSS

referenced by:

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 ::= SUM

referenced by:

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 ::= PRODUCT

referenced by:

min:

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

MIN

min ::= MIN

referenced by:

max:

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

MAX

max ::= MAX

referenced by:

avg:

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

AVG

avg ::= AVG

referenced by:

stddev:

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

STDDEV

stddev ::= STDDEV

referenced by:

count:

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

COUNT

count ::= COUNT

referenced by:

listagg:

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

LISTAGG

listagg ::= LISTAGG

referenced by:

asc:

ASC

asc ::= ASC

referenced by:

•sortDirection

desc:

DESC

desc ::= DESC

referenced by:

•sortDirection

joinConditions:

ON booleanExpression

joinConditions
::= ON booleanExpression

referenced by:

selectList:

selectPart COMMA

selectList
::= selectPart ( COMMA selectPart )*

referenced by:

selectPart:

part aliased labeled

selectPart
::= part aliased? labeled?

referenced by:

•selectList

aliased:

AS alias

aliased ::= AS? alias

referenced by:

•selectPart

labeled:

LABEL stringConstant

labeled ::= LABEL stringConstant

referenced by:

•selectPart

part:

expression aggregateFunction allColumnsSpec

part ::= expression
| aggregateFunction
| allColumnsSpec

referenced by:

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

•selectPart

aggregateFunction:

sum product avg stddev parenthesisOpen distinct min max parenthesisOpen arithmeticExpression count parenthesisOpen distinct part listagg parenthesisOpen distinct arithmeticExpressionList parenthesisClose WITHIN GROUP parenthesisOpen orderBy parenthesisClose

referenced by:

•part

allColumnsSpec:

allColumnsSpecId allColumnsSpecColumnNamePrefix allColumnsSpecColumnNamePostfix allColumnsSpecLabelPrefix allColumnsSpecLabelPostfix

referenced by:

•part

allColumnsSpecId:

alias DOT ASTERIX

allColumnsSpecId
::= ( alias DOT )? ASTERIX

referenced by:

allColumnsSpecColumnNamePrefix:

PREFIX WITH stringConstant

allColumnsSpecColumnNamePrefix
::= PREFIX WITH stringConstant

referenced by:

allColumnsSpecColumnNamePostfix:

POSTFIX WITH stringConstant

allColumnsSpecColumnNamePostfix
::= POSTFIX WITH stringConstant

referenced by:

allColumnsSpecLabelPrefix:

LABEL PREFIX WITH stringConstant

allColumnsSpecLabelPrefix
::= LABEL PREFIX WITH stringConstant

referenced by:

allColumnsSpecLabelPostfix:

LABEL POSTFIX WITH stringConstant

allColumnsSpecLabelPostfix
::= LABEL POSTFIX WITH stringConstant

referenced by:

ddlStatement:

createTableStatement dropTableStatement alterPersistentCacheStatement

ddlStatement
::= createTableStatement
| dropTableStatement
| alterPersistentCacheStatement

referenced by:

alterPersistentCacheStatement:

Besides an in-memory cache valid during the duration of a session, Invantive UniversalSQL offers an integrated cache storing data persistently using an on-premise or cloud relation database such as SQL Server or PostgreSQL. When configured, Invantive UniversalSQL 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 UniversalSQL 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. Invantive UniversalSQL installs a repository consisting of a few tables. The repository tables have names starting with 'dc_'.

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 can create and maintain a 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.

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

alterPersistentCacheSetStatement alterPersistentCacheDownloadStatement alterPersistentCachePurgeStatement alterPersistentCacheRefreshStatement alterPersistentCacheLoadStatement alterPersistentCacheTableRefreshStatement alterPersistentCachePartitionRefreshStatement alterPersistentCacheDropStatement

referenced by:

•ddlStatement

alterPersistentCachePurgeStatement:

ALTER PERSISTENT CACHE PURGE UNKNOWN OBSOLETE READY DROPPABLE ALL TABLE PARTITION VERSIONS

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

referenced by:

alterPersistentCacheDownloadStatement:

ALTER PERSISTENT CACHE DOWNLOAD FEED LICENSE CONTRACT CODE stringConstant DATA_CONTAINER stringConstant PARTITION partitionSimpleIdentifier LIMIT numericConstant

alterPersistentCacheDownloadStatement
::= ALTER PERSISTENT CACHE DOWNLOAD FEED ( LICENSE CONTRACT CODE stringConstant )? ( DATA_CONTAINER stringConstant )? ( PARTITION partitionSimpleIdentifier )? ( LIMIT numericConstant )?

referenced by:

alterPersistentCacheRefreshStatement:

ALTER PERSISTENT CACHE FORCE REFRESH DATA_CONTAINER dataContainerAlias PARALLEL numericConstant

alterPersistentCacheRefreshStatement
::= ALTER PERSISTENT CACHE FORCE? REFRESH ( DATA_CONTAINER dataContainerAlias? )? ( PARALLEL numericConstant )?

referenced by:

alterPersistentCacheLoadStatement:

ALTER PERSISTENT CACHE LOAD

alterPersistentCacheLoadStatement
::= ALTER PERSISTENT CACHE LOAD

referenced by:

alterPersistentCacheTableRefreshStatement:

ALTER PERSISTENT CACHE TABLE tableSpec FORCE REFRESH PARTITION partitionIdentifier PARALLEL numericConstant

alterPersistentCacheTableRefreshStatement
::= ALTER PERSISTENT CACHE TABLE tableSpec FORCE? REFRESH ( PARTITION partitionIdentifier )? ( PARALLEL numericConstant )?

referenced by:

alterPersistentCachePartitionRefreshStatement:

ALTER PERSISTENT CACHE PARTITION partitionIdentifier FORCE REFRESH PARALLEL numericConstant

alterPersistentCachePartitionRefreshStatement
::= ALTER PERSISTENT CACHE PARTITION partitionIdentifier FORCE? REFRESH ( PARALLEL numericConstant )?

referenced by:

alterPersistentCacheDropStatement:

ALTER PERSISTENT CACHE DROP TABLE tableSpec PARTITION partitionIdentifier PARTITION partitionIdentifier DATA_CONTAINER stringConstant

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

referenced by:

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 booleanConstant AUTO UPGRADE ONCE alterPersistentCacheSetTableOptions

referenced by:

alterPersistentCacheSetTableOptions:

TABLE tableSpec LOGICAL OVERALL VIEW MAINTAIN booleanConstant NAME stringConstant PARTITION VIEW MAINTAIN booleanConstant NAME PREFIX POSTFIX stringConstant STATE OBSOLETE DROPPED PARTITION partitionIdentifier APPROACH COPY TRICKLE SAMPLE

referenced by:

createTableStatement:

CREATE orReplace TABLE tableSpec AS selectStatement

createTableStatement
::= CREATE orReplace? TABLE tableSpec AS selectStatement

referenced by:

•ddlStatement

dropTableStatement:

DROP TABLE tableSpec

dropTableStatement
::= DROP TABLE tableSpec

referenced by:

•ddlStatement

orReplace:

OR REPLACE

orReplace
::= OR REPLACE

referenced by:

•createTableStatement

setStatement:

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

SET setIdentifier expression

setStatement
::= SET setIdentifier expression

referenced by:

transactionStatement
::= beginTransactionStatement
| rollbackTransactionStatement
| commitTransactionStatement

setIdentifier:

attributeIdentifier distributedAliasDirective

setIdentifier
::= attributeIdentifier distributedAliasDirective?

referenced by:

•setStatement

transactionStatement:

beginTransactionStatement rollbackTransactionStatement commitTransactionStatement

referenced by:

executeFileStatement:

FILE_PATH

executeFileStatement
::= FILE_PATH

referenced by:

beginTransactionStatement:

A begin transaction statement initiates a transaction. Invantive UniversalSQL 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.

BEGIN TRANSACTION

beginTransactionStatement
::= BEGIN TRANSACTION?

referenced by:

•transactionStatement

rollbackTransactionStatement:

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

ROLLBACK TRANSACTION

rollbackTransactionStatement
::= ROLLBACK TRANSACTION?

referenced by:

•transactionStatement

commitTransactionStatement:

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

COMMIT TRANSACTION

commitTransactionStatement
::= COMMIT TRANSACTION?

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 UniversalSQL 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'.

USE partitionIdentifiersList selectStatement

useStatement
::= USE ( partitionIdentifiersList | selectStatement )

referenced by:

•alterPersistentCacheDropStatement

partitionIdentifiersList:

partitionIdentifierWithAlias COMMA

partitionIdentifiersList
::= partitionIdentifierWithAlias ( COMMA partitionIdentifierWithAlias )*

referenced by:

•useStatement

partitionIdentifier:

parameterExpression numericConstant identifier ALL DEFAULT

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

referenced by:

•alterPersistentCachePartitionRefreshStatement

•alterPersistentCacheTableRefreshStatement

•partitionIdentifierWithAlias

partitionIdentifierWithAlias:

partitionIdentifier distributedAliasDirective

partitionIdentifierWithAlias
::= partitionIdentifier distributedAliasDirective?

referenced by:

•partitionIdentifiersList

partitionSimpleIdentifier:

numericConstant identifier

partitionSimpleIdentifier
::= numericConstant
| identifier

referenced by:

•alterPersistentCacheDownloadStatement

insertStatement:

bulk insert into tableSpec insertFieldList valuesExpression insertFieldList selectStatement identifiedByClause attachToClause

insertStatement
::= bulk? insert into tableSpec ( insertFieldList valuesExpression | insertFieldList? selectStatement ) identifiedByClause? attachToClause?

referenced by:

valuesExpression:

values_ insertValues

valuesExpression
::= values_ insertValues

referenced by:

bulk:

BULK

bulk ::= BULK

referenced by:

into:

INTO

into ::= INTO

referenced by:

insert:

INSERT

insert ::= INSERT

referenced by:

values_:

VALUES

values_ ::= VALUES

referenced by:

•valuesExpression

insertFieldList:

parenthesisOpen columnList parenthesisClose

insertFieldList
::= parenthesisOpen columnList parenthesisClose

referenced by:

insertValues:

parenthesisOpen insertValuesList parenthesisClose

insertValues
::= parenthesisOpen insertValuesList parenthesisClose

referenced by:

•valuesExpression

insertValuesList:

arithmeticExpression COMMA

insertValuesList
::= arithmeticExpression ( COMMA arithmeticExpression )*

referenced by:

•insertValues

identifiedByClause:

IDENTIFIED BY arithmeticExpression

identifiedByClause
::= IDENTIFIED BY arithmeticExpression

referenced by:

attachToClause:

ATTACH TO arithmeticExpression

attachToClause
::= ATTACH TO arithmeticExpression

referenced by:

updateStatement:

UPDATE FROM tableSpec SET updateValuesList whereClause

updateStatement
::= UPDATE FROM? tableSpec SET updateValuesList whereClause?

referenced by:

updateValuesList:

updateValue COMMA

updateValuesList
::= updateValue ( COMMA updateValue )*

referenced by:

•updateStatement

updateValue:

column EQ arithmeticExpression

updateValue
::= column EQ arithmeticExpression

referenced by:

•updateValuesList

deleteStatement:

delete FROM tableSpec whereClause

deleteStatement
::= delete FROM? tableSpec whereClause?

referenced by:

delete:

DELETE

delete ::= DELETE

referenced by:

•deleteStatement

expression:

booleanExpression arithmeticExpression

expression
::= booleanExpression
| arithmeticExpression

referenced by:

•caseElseExpression

•pSqlExecuteImmediateStatement

•pSqlAssignmentStatement

•part

booleanExpression:

not booleanExpression and or booleanExpression parenthesisOpen booleanExpression parenthesisClose predicateExpression true false

referenced by:

•expression

•pSqlWhileLoopStatement

•whereClause

caseExpression:

case caseWhenThenExpression caseElseExpression end

caseExpression
::= case caseWhenThenExpression+ caseElseExpression? end

referenced by:

caseWhenThenExpression:

when expression then arithmeticExpression

caseWhenThenExpression
::= when expression then arithmeticExpression

referenced by:

caseElseExpression:

else expression

caseElseExpression
::= else expression

referenced by:

parenthesisOpen:

PARENTHESIS_OPEN

parenthesisOpen
::= PARENTHESIS_OPEN

referenced by:

•now

•utc

parenthesisClose:

PARENTHESIS_CLOSE

parenthesisClose
::= PARENTHESIS_CLOSE

referenced by:

•now

•utc

case:

CASE

case ::= CASE

referenced by:

when:

WHEN

when ::= WHEN

referenced by:

then:

THEN

then ::= THEN

referenced by:

else:

ELSE

else ::= ELSE

referenced by:

•caseElseExpression

end:

END

end ::= END

referenced by:

not:

NOT

not ::= NOT

referenced by:

•isLikeComparingExpression

•isNullComparingExpression

is:

is ::= IS

referenced by:

•isNullComparingExpression

are:

ARE

are ::= ARE

referenced by:

•isEqualComparingExpression

and:

AND

and ::= AND

referenced by:

or:

or ::= OR

referenced by:

true:

TRUE

true ::= TRUE

referenced by:

•booleanConstant

false:

FALSE

false ::= FALSE

referenced by:

•booleanConstant

predicateExpression:

arithmeticExpression not in_ parenthesisOpen arithmeticExpression COMMA inSelectStatement parenthesisClose between arithmeticExpression and arithmeticExpression gt ge lt le eq neq arithmeticExpression isNullComparingExpression isLikeComparingExpression isEqualComparingExpression

referenced by:

parameterExpression:

COLON identifier

parameterExpression
::= COLON identifier

referenced by:

•partitionIdentifier

gt:

Greater then is a binary operator which returns true when the left value is greater than the right value. When one of both values is null, the outcome is null. Otherwise it is false.

gt ::= GT

referenced by:

ge:

Greater or equal is a binary operator which returns true when the left value is greater than or equal to the right value. When one of both values is null, the outcome is null. Otherwise it is false.

ge ::= GE

referenced by:

lt:

Less then is a binary operator which returns true when the left value is less than the right value. When one of both values is null, the outcome is null. Otherwise it is false.

lt ::= LT

referenced by:

le:

Less or equal is a binary operator which returns true when the left value is less than or equal to the right value. When one of both values is null, the outcome is null. Otherwise it is false.

le ::= LE

referenced by:

eq:

eq ::= EQ

referenced by:

neq:

NEQ

neq ::= NEQ

referenced by:

like:

like ::= LIKE

referenced by:

•isLikeComparingExpression

between:

BETWEEN

between ::= BETWEEN

referenced by:

in_:

in_ ::= IN

referenced by:

isNullComparingExpression:

is not NULL

isNullComparingExpression
::= is not? NULL

referenced by:

isEqualComparingExpression:

are EQUAL

isEqualComparingExpression
::= are? EQUAL

referenced by:

isLikeComparingExpression:

not like arithmeticExpression

isLikeComparingExpression
::= not? like arithmeticExpression

referenced by:

arithmeticExpression:

minus plus arithmeticExpression times divide plus minus concat arithmeticExpression parenthesisOpen arithmeticExpression selectStatement parenthesisClose functionExpression parameterExpression caseExpression fieldIdentifier constant

referenced by:

•arithmeticExpressionList

•attachToClause

•expression

•identifiedByClause

•insertValuesList

•isLikeComparingExpression

•updateValue

arithmeticExpressionList:

arithmeticExpression list

arithmeticExpressionList
::= arithmeticExpression ( list arithmeticExpression )*

referenced by:

functionExpression:

abs acos anonymize ascii asin atan atan2 base64_decode base64_encode bit_length octet_length camel ceil chr coalesce concat_func cos covfefify compress uncompress dateadd datepart date_ceil date_floor date_round date_trunc day dayofweek dayofyear dense_rank double_metaphone double_metaphone_alt exp_func floor from_unixtime hour httpget httpget_text httppost initcap instr jsondecode jsonencode left length levenshtein ln log lower lpad ltrim md5 metaphone metaphone3 metaphone3_alt microsecond millisecond minute mod month newid number_to_speech normalize nvl power quarter quote_ident quote_literal quote_nullable raise_error random random_blob rand rank regexp_instr regexp_replace regexp_substr remainder replace repeat reverse right round row_number rpad rtrim second sin soundex sqrt substr sys_context tan to_binary to_char to_date to_number to_guid to_hex translate translate_resources trim trunc unistr unix_timestamp upper urldecode urlencode user unzip zip xmlcomment xmldecode xmlencode xmlelement xmlformat xmltransform year add_months zero_blob parenthesisOpen arithmeticExpressionList parenthesisClose random rand row_number now utc user

referenced by:

abs:

Returns the absolute value of a double-precision floating-point number.

Parameters:

•Input: A number that is greater than or equal to System.Double.MinValue, but less than or equal to System.Double.MaxValue.

Returns: A double-precision floating-point number. ABS

abs ::= ABS

referenced by:

acos:

Returns the angle of the provided cosine.

Parameters:

•Input: the cosine to get the angle of.

Returns: A number which represents the angle of the provided cosine. ACOS

acos ::= ACOS

referenced by:

anonymize:

Anonymize a text or number. Anonymization is executed such that when the same original value is anonymized within the same session, the anonymized value will be identical. The anonymized value also uniquely matches the original value. With no access to the anonymization map however, the original value can however not be calculated from the anonymized value.

In mathematics, the anonymization function is a bijection: each element of the original set is paired with exactly one element of the anonymized set, and each element of the anonymized set is paired with exactly one element of the original set.

Parameters:

•Value: A text or number to be obfuscated.

•Maximum length (optional): Maximum length in digits for numbers or characters for text of anonymized value. Null means no restriction on maximum length.

•Mapping (optional): algorithm to use. The default algorithm is 'DEFAULT' which maps text values to a range of hexadecimal characters and numbers to a range of numbers. Alternative mappings are described below.

The following anonymization maps are available on installation:

•DEFAULT: the default algorithm.

•IVE-GL-JOURNAL-DESCRIPTION: general ledger journal descriptions: no preferred anonymizations, leave familiar and non-confidential descriptions in original state.

•IVE-GL-ACCOUNT-DESCRIPTION: general ledger account descriptions: no preferred anonymizations, leave familiar and non-confidential descriptions in original state.

•IVE-PSN-FIRST-NAME: person first names: prefer readable alternative first names, anonymize all.

•IVE-PSN-LAST-NAME: person last names: prefer readable alternative last names, anonymize all.

•IVE-ADS-CITY-NAME: address city names: prefer readable alternative city names, anonymize all.

•IVE-ADS-STREET-NAME: address street names: prefer readable alternative street names, anonymize all.

The data dictionary contains the anonymization maps used sofar in the session and their corresponding values:

select * from SystemAnonymizationMaps@DataDictionary select * from SystemAnonymizationMapValues@DataDictionary select * from SystemAnonymizationPredefinedMaps@DataDictionary

Returns: Anonymized value. ANONYMIZE

anonymize
::= ANONYMIZE

referenced by:

ascii:

Get the position of a character on database character set.

Parameters:

•Input: character to get position from.

Returns: The position of the character on database character set. ASCII

ascii ::= ASCII

referenced by:

asin:

Returns the angle of the provided sine.

Parameters:

•Input: the sine to get the angle of.

Returns: A number which represents the angle of the provided sine. ASIN

asin ::= ASIN

referenced by:

atan:

Returns the angle of the provided tangent.

Parameters:

•Input: the tangent to get the angle of.

Returns: A number which represents the angle of the provided tangent. ATAN

atan ::= ATAN

referenced by:

atan2:

Returns the angle of the provided tangent.

Parameters:

•First number: the first number to get the angle of.

•Second number: the second to get the angle of.

Returns: A number which represents the angle of the provided tangent. ATAN2

atan2 ::= ATAN2

referenced by:

add_months:

Add an amount of months to a datetime.

Parameters:

•Date: datetime to ass the months to.

•Months: the amount of months to add.

Returns: A new datetime with the amount of months added. ADD_MONTHS

add_months
::= ADD_MONTHS

referenced by:

base64_decode:

Converts the base64_encoded value back to the binairy value as defined on Wikipedia.

Parameters:

•Input: value to convert back to the original.

Returns: The input decoded back to the binairy value. BASE64_DECODE

base64_decode
::= BASE64_DECODE

referenced by:

base64_encode:

Converts a binairy value to base64_encoded characters as defined on Wikipedia.

Parameters:

•Input: value to convert to base64 characters.

Returns: The input encoded to base64 characters. BASE64_ENCODE

base64_encode
::= BASE64_ENCODE

referenced by:

camel:

Converts provided string to Camel case.

Parameters:

•Input: the string that will be converted to Camel case.

Returns: A string converted to Camel case. CAMEL

camel ::= CAMEL

referenced by:

ceil:

Rounds the input to the largest following integer. Unless an amount of decimals is defined, in which case it rounds to the largest integer number with the amount of decimals or date with the amount of positions.

Parameters:

•Input: A number or datetime to ceil.

•Decimals [optional]: A number to specify how many decimals it may ceil to in case of a number. In case of a datetime, it reflects the number of time positions, ranging from -2 for years to 2 for minutes.

Returns: The ceiling of the input. CEIL

ceil ::= CEIL

referenced by:

chr:

Get a character from database character set.

Parameters:

•Input: a numeric value of a character.

Returns: A character from the database character set. CHR CHAR

chr ::= CHR
| CHAR

referenced by:

bit_length:

Get the number of bits needed to represent a value. For a blob, this is the number of bits for the bytes of the blob. For all other data types, the value is first converted to a string and then the number of bits of the UTF8 representation is determined.

Parameters:

•Value: value to determine length in bits for.

Returns: number of bits needed to represent the value. BIT_LENGTH

bit_length
::= BIT_LENGTH

referenced by:

octet_length:

Get the number of bytes needed to represent a value. For a blob, this is the number of bytes of the blob. For all other data types, the value is first converted to a string and then the number of bytes of the UTF8 representation is determined.

Parameters:

•Value: value to determine length in bytes for.

Returns: number of bytes needed to represent the value. OCTET_LENGTH

octet_length
::= OCTET_LENGTH

referenced by:

repeat:

Get a concatenation of the text by a number of times.

Parameters:

•Text: text to repeat.

•Times: number of time to repeat the text.

Returns: the text repeated a number of times. REPEAT

repeat ::= REPEAT

referenced by:

raise_error:

RAISE_ERROR

raise_error
::= RAISE_ERROR

referenced by:

coalesce:

Performs a coalescing operation.

Parameters:

•Left: an object.

•Right: an object.

Returns: the left value if right is empty, otherwise the right value. COALESCE

coalesce ::= COALESCE

referenced by:

concat:

Concatenate the left and right values together as a text.

CONCAT_OP

concat ::= CONCAT_OP

referenced by:

concat_func:

Concatenate a list of values together as a text.

CONCAT

concat_func
::= CONCAT

referenced by:

cos:

Returns the cosine of the provided angle.

Parameters:

•Input: the angle to get the cosine of.

Returns: A number which represents the cosine of the provided angle. COS

cos ::= COS

referenced by:

covfefify:

COVFEFIFY

covfefify
::= COVFEFIFY

referenced by:

compress:

COMPRESS

compress ::= COMPRESS

referenced by:

uncompress:

UNCOMPRESS

uncompress
::= UNCOMPRESS

referenced by:

dateadd:

Adds an amount of time to a date.

Parameters:

•Interval: the date interval to be added.

•Number: the number of intervals to add.

•Date: the date to wich the interval should be added.

Returns: The original date with the number of intervals added. DATEADD

dateadd ::= DATEADD

referenced by:

datepart:

Get the specified datepart from a datetime.

Parameters:

•datepart: a part of a date.

•date: a datetime to get the datepart from.

Returns: a part of a datetime. DATEPART

datepart ::= DATEPART

referenced by:

date_ceil:

DATE_CEIL

date_ceil
::= DATE_CEIL

referenced by:

date_floor:

DATE_FLOOR

date_floor
::= DATE_FLOOR

referenced by:

date_round:

DATE_ROUND

date_round
::= DATE_ROUND

referenced by:

date_trunc:

DATE_TRUNC

date_trunc
::= DATE_TRUNC

referenced by:

day:

Collect the day from a date.

Parameters:

•Input: A dateTime.

Returns: The day as an integer. DAY

day ::= DAY

referenced by:

dayofweek:

Collect the day of a week from a date.

Parameters:

•Input: A dateTime.

Returns: The day of a week as an integer. DAYOFWEEK

dayofweek
::= DAYOFWEEK

referenced by:

dayofyear:

Collect the day of a year from a date.

Parameters:

•Input: A dateTime.

Returns: The day of a year as an integer. DAYOFYEAR

dayofyear
::= DAYOFYEAR

referenced by:

dense_rank:

DENSE_RANK

dense_rank
::= DENSE_RANK

referenced by:

double_metaphone:

DOUBLE_METAPHONE

double_metaphone
::= DOUBLE_METAPHONE

referenced by:

double_metaphone_alt:

DOUBLE_METAPHONE_ALT

double_metaphone_alt
::= DOUBLE_METAPHONE_ALT

referenced by:

divide:

Divide one number by the second number.

Parameters:

•first: a number to divide.

•second: a number to divide with.

Returns: the divided output. DIVIDE

divide ::= DIVIDE

referenced by:

exp:

Returns the provided number raised to the specified power.

Parameters:

•Input: the number to raise by the specified power.

Returns: A number which is the provided number raised to the specified power. EXP_OP

exp ::= EXP_OP

no references

exp_func:

EXP

exp_func ::= EXP

referenced by:

floor:

Rounds the input to the smallest following integer. Unless an amount of decimals is defined, in which case it rounds to the smallest integer with the amount of decimals or date with the amount of positions.

Parameters:

•Input: A number or datetime to floor.

•Decimals [optional]: A number to specify how many decimals it may floor to in case of a number. In case of a datetime, it reflects the number of time positions, ranging from -2 for years to 2 for minutes.

Returns: The floor of the input. FLOOR

floor ::= FLOOR

referenced by:

from_unixtime:

Get the date/time from an integer representing a UNIX epoch time.

Parameters:

•Input: An integer.

Returns: The date/time which the UNIX epoch time represents. FROM_UNIXTIME

from_unixtime
::= FROM_UNIXTIME

referenced by:

hour:

Collect the hour from a date.

Parameters:

•Input: A dateTime.

Returns: The hour as an integer. HOUR

hour ::= HOUR

referenced by:

initcap:

Changes the first letter of each word in uppercase, all other letters in lowercase.

Parameters:

•Input: Text to convert.

Returns: The input with the first letter of each word in uppercase. INITCAP

initcap ::= INITCAP

referenced by:

instr:

Get a number which is a position of the first occurrence of substring in the string.

Parameters:

•String: String to be searched.

•Substring: Text to search for.

•StartPosition [optional]: Position of string to start searching.

•occurrence [optional]: Return the position of the occurrence.

Returns: The position of the substring inside the original string. INSTR

instr ::= INSTR

referenced by:

jsondecode:

JSONDECODE

jsondecode
::= JSONDECODE

referenced by:

jsonencode:

JSONENCODE

jsonencode
::= JSONENCODE

referenced by:

length:

Gets the number of characters in provided string.

Parameters:

•Input: the string to get the length of.

Returns: A number which represents the number of characters in the provided string. LENGTH

length ::= LENGTH

referenced by:

levenshtein:

Determine the Levenshtein distance between two values as defined on Wikipedia.

LEVENSHTEIN

levenshtein
::= LEVENSHTEIN

referenced by:

list:

COMMA

list ::= COMMA

referenced by:

•arithmeticExpressionList

ln:

Get the natural logarithm of a number.

Parameters:

•Input: a number to get the natural logarithm from.

Returns: The natural logarithm of the input. LN

ln ::= LN

referenced by:

log:

Get the natural logarithm of a number in a specified base.

Parameters:

•Input: a number to get the natural logarithm from.

•Base [optional]: the base to get the natural logarithm from.

Returns: The natural logarithm of the input in the specified base. LOG

log ::= LOG

referenced by:

lower:

Converts provided string to lowercase.

Parameters:

•Input: the string that will be converted to lowercase.

Returns: A string converted to lowercase. LOWER

lower ::= LOWER

referenced by:

lpad:

Pad a string to the left to make it a specified length.

Parameters:

•Input: string to be padded.

•Lenght: the length the string should be padded to.

•Characters [optional]: Characters to pad with.

Returns: A string padded to the left to a given length with the optional specified characters. LPAD

lpad ::= LPAD

referenced by:

ltrim:

Trims characters from the left side of a string.

Parameters:

•Input: the string from to trim characters from the left side.

•(Optinial) Chars to trim: the character to trim. Default is " ".

Returns: A string with chars trimmed from the left. LTRIM

ltrim ::= LTRIM

referenced by:

md5:

Converts a value to a 128-bit hash value as defined on Wikipedia.

Parameters:

•Input: Text to convert with MD5.

Returns: The input converted with MD5. MD5

md5 ::= MD5

referenced by:

metaphone:

Converts a value to the Metaphone code as defined on Wikipedia.

Parameters:

•Input: value to convert to metaphone.

•Length: maximum output length of the given input.

Returns: The input converted to metaphone, with a given output length. METAPHONE

metaphone
::= METAPHONE

referenced by:

metaphone3:

METAPHONE3

metaphone3
::= METAPHONE3

referenced by:

metaphone3_alt:

METAPHONE3_ALT

metaphone3_alt
::= METAPHONE3_ALT

referenced by:

mod:

Get the remainder of a divide calculation.

Parameters:

•dividend: a number.

•divider: a number.

Returns: The remainder. MOD

mod ::= MOD

referenced by:

minus:

Subtracts a value from another.

Parameters:

•Value: a number or datetime.

•Subtract: a number or datetime.

Returns: The value minus the subtraction. MINUS

minus ::= MINUS

referenced by:

minute:

Collect the minute from a date.

Parameters:

•Input: A dateTime.

Returns: The minute as an integer. MINUTE

minute ::= MINUTE

referenced by:

month:

Collect the month from a date.

Parameters:

•Input: A dateTime.

Returns: The month as an integer. MONTH

month ::= MONTH

referenced by:

newid:

Creates a new Guid id.

Returns: The new Guid id.

NEWID

newid ::= NEWID

referenced by:

nvl:

Coalesce all values together.

Returns: All values coalesced together.

NVL

nvl ::= NVL

referenced by:

plus:

Adding a value to another.

Parameters:

•Value: a number or datetime.

•add: a number or datetime.

Returns: A new value with both values added to eachother. PLUS

plus ::= PLUS

referenced by:

power:

Gets a value of a number raised to another.

Parameters:

•Value: a number.

•exponent: a number.

Returns: The value of a number raised to another. POWER

power ::= POWER

referenced by:

random:

Generates a random number between 0 and 1.

Parameters:

•Seed: Produce a repeatable sequence of random numbers each time that seed value is provided.

Returns: A random number between 0 and 1. RANDOM

random ::= RANDOM

referenced by:

random_blob:

Generates a blob with pseudo-random values.

Parameters:

•Length: Produce a blob with this length in terms of bytes.

Returns: A blob with pseudo-random values. RANDOM_BLOB

random_blob
::= RANDOM_BLOB

referenced by:

rand:

RAND

rand ::= RAND

referenced by:

rank:

RANK

rank ::= RANK

referenced by:

regexp_substr:

Extracts a substring from the given value using regular expression.

Parameters:

•Input: The text to get the substring from.

•Pattern: Regular expression pattern.

•Start position [optional]: The start index from the input.

•Appearance [optional]: Indicating the appearance of the substr operation.

•Match_parameter [optional]: A text literal that lets you change the default matching behavior of the function.

Returns: The substring from the input. REGEXP_SUBSTR

regexp_substr
::= REGEXP_SUBSTR

referenced by:

regexp_instr:

Determine the position of the regular expression in the given value. Returns 0 when the regular expression is not contained in the given value.

Parameters:

•Input: The text to get the regular expression position from.

•Pattern: Regular expression pattern.

•Start position [optional]: The start index from the input.

•Appearance [optional]: Indicating the appearance of the instr operation.

•ReturnOption [optional]: Select either the first character found or the first character after the occurrence of the pattern.

•Match_parameter [optional]: A text literal that lets you change the default matching behavior of the function.

Returns: The location of a regular expression pattern in the input. REGEXP_INSTR

regexp_instr
::= REGEXP_INSTR

referenced by:

regexp_replace:

Replaces all occurrences matching the regular expression with the replacement value. The replacement value may contain references to matches in the regular expression by using the dollar-sign ('$') plus the reference number.

Parameters:

•Input: The text to get the substring from.

•Pattern: Regular expression pattern.

•Replacement [optional]: Text to replace with.

•Start position [optional]: The start index from the input.

•Appearance [optional]: Indicating the appearance of the replace operation.

•Match_parameter [optional]: A text literal that lets you change the default matching behavior of the function. The available options are 'c' for case-sensitive, 'i' for ignore case, 'n' for single-line, 'm' for multi-line and 'x' for ignore pattern white space.

Returns: The input with every occurrence of the regular expression pattern replaced with the replacement.

REGEXP_REPLACE

regexp_replace
::= REGEXP_REPLACE

referenced by:

remainder:

Get the remainder of a divide calculation.

The REMAINDER function uses the round function in its formula, whereas the MOD function uses the floor function in its formula.

Parameters:

•Number1: a number.

•Number2: a number.

Returns: The remainder. REMAINDER

remainder
::= REMAINDER

referenced by:

replace:

Replaces a string with string in given string.

Parameters:

•Input: the string to replace a string in.

•Old text: the string to be replaced.

•New text: the string which 'Old text' will be replaced with.

Returns: A string with the replaced string. REPLACE

replace ::= REPLACE

referenced by:

reverse:

Flips the input around.

Parameters:

•Input: text to flip around.

Returns: The text with it's characters in reversed order. REVERSE

reverse ::= REVERSE

referenced by:

round:

Rounds the input to the closest following integer. Unless an amount of decimals is defined, in which case it rounds to the closest integer number with the amount of decimals or date with the amount of positions.

Parameters:

•Input: A number or datetime to round.

•Decimals [optional]: A number to specify how many decimals it may round to in case of a number. In case of a datetime, it reflects the number of time positions, ranging from -2 for years to 2 for minutes.

Returns: The rounded input. ROUND

round ::= ROUND

referenced by:

row_number:

ROW_NUMBER

row_number
::= ROW_NUMBER

referenced by:

rpad:

Rightpad function pads the right-side of a string with a specific set of characters to the given length. When no set of characters given, it will pad with a whitespace.

Parameters:

•Input: Text to be padded.

•Length: The length to make the input to.

•Pad text [optional]: Text to add to the input if the length is larger then the input.

Returns: The padded text, or null if the string cannot be padded. RPAD

rpad ::= RPAD

referenced by:

rtrim:

Trims characters from the right side of a string.

Parameters:

•Input: the string from which to trim characters from the right side.

•(Optional) Chars to trim: the character to trim. Default is " ".

Returns: A string with chars trimmed from the right. RTRIM

rtrim ::= RTRIM

referenced by:

microsecond:

Collect the microsecond from a date.

Parameters:

•Input: A dateTime.

Returns: The microsecond as an integer. MICROSECOND

microsecond
::= MICROSECOND

referenced by:

millisecond:

Collect the millisecond from a date.

Parameters:

•Input: A dateTime.

Returns: The millisecond as an integer. MILLISECOND

millisecond
::= MILLISECOND

referenced by:

number_to_speech:

NUMBER_TO_SPEECH

number_to_speech
::= NUMBER_TO_SPEECH

referenced by:

normalize:

Normalize a file path by replacing all invalid and non-ASCII characters for use in a file path by underscore. After that, the file path is made more readable by various operations such as removal of duplicate whitespace and underscore characters.

Parameters:

•Original file path: path of the file.

•Maximum file name length: length in characters into which the normalized file name must fit.

•Allow path separator: whether to allow the path separator '\' in the normalized file name. When not, occurrences are replaced.

Returns: a normalized file path. NORMALIZE

normalize
::= NORMALIZE

referenced by:

second:

Collect the second from a date.

Parameters:

•Input: A dateTime.

Returns: The second as an integer. SECOND

second ::= SECOND

referenced by:

soundex:

Converts a value to the Soundex code as defined on Wikipedia.

Parameters:

•Input: Text to that retrieve the soundex value from.

Returns: A text started with a number and followed by 3 digits. SOUNDEX

soundex ::= SOUNDEX

referenced by:

sin:

Returns the sine of the provided angle.

Parameters:

•Input: the angle to get the sine of.

Returns: A number which represents the sine of the provided angle. SIN

sin ::= SIN

referenced by:

sqrt:

Returns the square root of the provided number.

Parameters:

•Input: the number to get the square root of.

Returns: A number which represents the square root of the provided number. SQRT

sqrt ::= SQRT

referenced by:

substr:

Gets a substring from the input.

Parameters:

•Input: text to gather the substring from.

•Start: start position.

•Lenght: maximum lenght of the substring.

Returns: The substring from the original input. SUBSTR

substr ::= SUBSTR

referenced by:

sys_context:

Text value of a parameter associated with a context.

Parameters:

•context: a namespace.

•parameter: name of the parameter.

Solely the namespace USERENV is available with the following parameter names:

•APPLICATION_VERSION: version of the client application.

•APPLICATION_FULL: name and version of the client application.

•APPLICATION_BUILD_EXPIRATION_DATE: build expiration date of the client application.

•AUTHENTICATION_METHOD: current authentication method.

•CLIENT_IP_ADDRESS_INTERNAL: internal IP address of the client device.

•CLIENT_IP_ADDRESS_EXTERNAL: external IP address of the client device.

•CLIENT_LOGICAL_CORE_COUNT: number of logical processor cores in the client device.

•CLIENT_MACHINE_NAME: machine name of the client device.

•CLIENT_SYSTEM_64_BIT: whether the OS is 64-bit on the client device.

•CLIENT_SYSTEM_NAME: full OS name running on the client device.

•CLIENT_SYSTEM_DIRECTORY: system directory of the client device.

•CLIENT_SYSTEM_PAGE_SIZE: system page size of the client device.

•CLIENT_VIRTUAL_MACHINE: whether the client device is a virtual machine.

•CLR_VERSION_BUILD: build version of the Common Language Runtime.

•CLR_VERSION_MAJOR: major version of the Common Language Runtime.

•CLR_VERSION_MAJOR_REVISION: major revision of the Common Language Runtime.

•CLR_VERSION_MINOR: minor version of the Common Language Runtime.

•CLR_VERSION_MIN_REVISION: minor revision of the Common Language Runtime.

•COMPANY_ID: ID of the company of current user.

•COMPANY_NAME: name of the company of current user.

•COMPANY_PHONE: phone of the company of current user.

•COMPANY_WEB_SITE: web site of the company of current user.

•DATA_CONTAINER_ALIAS: alias of active data container.

•DATA_CONTAINER_ID: ID of active data container.

•DATABASE_DESCRIPTION: description of database.

•DATABASE_FULL_NAME: full name of database.

•DATABASE_VERSION: version of database.

•LANG: ISO abbreviation for the language name of the user. Alternative: USER_LANGUAGE_CODE.

•MODULE: name of the client application. Alternative: APPLICATION_NAME.

•PROCESS_64_BIT: whether the OS process on the client device runs as 64-bit.

•PROCESS_COMMAND_LINE: command line used to start the OS process.

•PROCESS_CURRENT_DIRECTORY: current directory of the OS process.

•PROCESS_STACK_TRACE: current stack trace of the OS process.

•PROCESS_WORKING_SET: working set of the OS process.

•PROVIDER_DESCRIPTION: description of active data container.

•PROVIDER_DOCUMENTATION_URL: documentation (URL) of active data container.

•PROVIDER_DOWNLOAD_IMPLEMENTATION_URL: download driver (URL) of active data container.

•PROVIDER_NAME: name of active data container.

•PROVIDER_SHORT_NAME: short name of active data container.

•PROVIDER_TECHNICAL_DOCUMENTATION_URL: technical documentation (URL) of active data container.

•SESSION_USER: log on code of the current user. Alternative: CURRENT_USER.

•SESSIONID: session ID of current session.

•USER_DOMAIN_NAME: Windows domain name of current user.

•USER_EMAIL_ADDRESS: email address of current user.

•USER_FIRST_NAME: first name of current user.

•USER_FULL_NAME: full name of current user.

•USER_GENDER: gender of current user.

•USER_HOME_DIRECTORY: home directory of current user on client device.

•USER_INTERACTIVE: whether the current user works interactive.

•USER_PICTURES_DIRECTORY: pictures directory of current user on client device.

•USER_FAVORITES_DIRECTORY: favorites directory of current user on client device.

•USER_DESKTOP_DIRECTORY: desktop directory of current user on client device.

•USER_DOCUMENTS_DIRECTORY: documents directory of current user on client device.

•USER_PROFILE_DIRECTORY: profile directory of current user on client device.

•USER_LAST_LOG_ON: time of last log on of current user.

•USER_LAST_NAME: last name of current user.

•USER_LINKED_IN: LinkedIn name of current user.

•USER_MIDDLE_NAME: middle name of current user.

•USER_MOBILE_NUMBER: mobile number of current user.

•USER_NATIONALITY: nationality of current user.

•USER_PHONE_NUMBER: phone number of current user.

•USER_PICTURE_URL: picturel (URL) of current user.

•USER_SKYPE: Skype name of current user.

•USER_TITLE: title of current user.

•USER_TWITTER: Twitter name of current user.

•USER_WEB_SITE: personal web site of current user.

Returns: Value of the parameter in the context namespace. SYS_CONTEXT

sys_context
::= SYS_CONTEXT

referenced by:

tan:

Returns the tangent of the provided angle.

Parameters:

•Input: the angle to get the tangent of.

Returns: A number which represents the tangent of the provided angle. TAN

tan ::= TAN

referenced by:

times:

Multiplies one number by the second number.

Parameters:

•First: a number to multiply.

•Second: a number to multiply with.

Returns: The first number multiplied by the second number. ASTERIX

times ::= ASTERIX

referenced by:

translate:

Translate replaces all occurrences of each character in from_string to its corresponding character in to_string.

Parameters:

•input: The string to replace a sequence of characters with another set of characters.

•from_string: The string that will be searched for in the input.

•to_string: All characters in the from_string will be replaced with the corresponding character in the to_string

Returns: the input with all occurrences of each character in from_string replaced by its corresponding character in to_string. TRANSLATE

translate
::= TRANSLATE

referenced by:

translate_resources:

Replace all Invantive-style resources ('{res:...}') by their translation in the current language.

Parameters:

•txt: The string to replace resources in.

Returns: the input with all resources replaced by their translation. TRANSLATE_RESOURCES

translate_resources
::= TRANSLATE_RESOURCES

referenced by:

trim:

Trims whitespaces from both sides of the provided string.

Parameters:

•Input: the string from which to trim characters.

Returns: A string trimmed from whitespaces from both sides. TRIM

trim ::= TRIM

referenced by:

trunc:

Calculates the integral part of a number. Unless an amount of decimals is defined, in which case it calculates to the integer with the amount of decimals or date with the amount of positions.

Parameters:

•Input: A number or datetime to truncate.

•Decimals [optional]: A number to specify how many decimals it may truncate to in case of a number. In case of a datetime, it reflects the number of time positions, ranging from -2 for years to 2 for minutes.

Returns: The truncated input. TRUNC

trunc ::= TRUNC

referenced by:

to_hex:

TO_HEX

to_hex ::= TO_HEX

referenced by:

unistr:

Converts a text with unicodes to regular characters.

Parameters:

•Input: text with unicodes.

Returns: The input converted to all regular characters. UNISTR

unistr ::= UNISTR

referenced by:

upper:

Converts provided string to uppercase.

Parameters:

•Input: the string that will be converted to uppercase.

Returns: A string converted to uppercase. UPPER

upper ::= UPPER

referenced by:

urldecode:

Decodes a url.

Parameters:

•Url: url to decode.

Returns: The decoded url. URLDECODE

urldecode
::= URLDECODE

referenced by:

urlencode:

Encodes a url.

Parameters:

•Url: url to encode.

Returns: The encoded url. URLENCODE

urlencode
::= URLENCODE

referenced by:

unix_timestamp:

Get the UNIX epoch time of a date/time.

Parameters:

•Input: A dateTime. Current date/time is used when no value is specified.

Returns: The UNIX epoch time. UNIX_TIMESTAMP

unix_timestamp
::= UNIX_TIMESTAMP

referenced by:

unzip:

UNZIP

unzip ::= UNZIP

referenced by:

zip:

ZIP

zip ::= ZIP

referenced by:

xmlcomment:

Format a text as an XML comment.

Parameters:

•Input: the input which will be formatted as XML comment.

Returns: A text with the input as XML comment. XMLCOMMENT

xmlcomment
::= XMLCOMMENT

referenced by:

xmldecode:

Returns the XML decoded input.

Parameters:

•Input: the input which will be decoded into XML.

Returns: An object which is the XML decoded input. XMLDECODE

xmldecode
::= XMLDECODE

referenced by:

xmlencode:

Returns the XML encoded input.

Parameters:

•Input: the input which will be encoded into XML.

Returns: An object which is the XML encoded input. XMLENCODE

xmlencode
::= XMLENCODE

referenced by:

xmlelement:

XMLELEMENT

xmlelement
::= XMLELEMENT

referenced by:

xmltransform:

Applies an XSL style sheet to the XML instance.

Parameters:

•XML: XML type instance to be transformed with the XSL style sheet.

•Style sheet: The XSL style sheet to apply.

Returns: The XML instance with the style sheet applied to it. XMLTRANSFORM

xmltransform
::= XMLTRANSFORM

referenced by:

xmlformat:

Pretty-print xml text.

Parameters:

•Xml: xml to pretty-print.

Returns: The pretty-printed XML text. XMLFORMAT

xmlformat
::= XMLFORMAT

referenced by:

httpget:

Collects all data from the URL as binary data.

The URL must be publicly accessible. Use the NativePlatformScalarRequest view on cloud applications to directly access their web APIs.

Parameters:

•URL: the URL to collect the data from.

Returns: The collected data as an byte array. HTTPGET

httpget ::= HTTPGET

referenced by:

httpget_text:

Collects all data from the URL as text.

The URL must be publicly accessible. Use the NativePlatformScalarRequest view on cloud applications to directly access their web APIs.

Parameters:

•URL: the URL to collect the data from.

•Encoding: the encoding from the data to receive, which is by default UTF8.

Returns: The collected data as text. HTTPGET_TEXT

httpget_text
::= HTTPGET_TEXT

referenced by:

httppost:

HTTPPOST

httppost ::= HTTPPOST

referenced by:

quarter:

Collect the quarter from a date.

Parameters:

•Input: A dateTime.

Returns: The quarter as an integer. QUARTER

quarter ::= QUARTER

referenced by:

quote_ident:

QUOTE_IDENT

quote_ident
::= QUOTE_IDENT

referenced by:

quote_literal:

QUOTE_LITERAL

quote_literal
::= QUOTE_LITERAL

referenced by:

quote_nullable:

QUOTE_NULLABLE

quote_nullable
::= QUOTE_NULLABLE

referenced by:

user:

Gets the user log on code.

Returns: The user log on code.

USER

user ::= USER

referenced by:

year:

Collect the year from a date.

Parameters:

•Input: A dateTime.

Returns: The year as an integer. YEAR

year ::= YEAR

referenced by:

to_binary:

TO_BINARY

to_binary
::= TO_BINARY

referenced by:

to_char:

Converts a value into text.

Parameters:

•Input: value to convert.

Returns: The input converted to text. TO_CHAR

to_char ::= TO_CHAR

referenced by:

to_date:

Converts a value into a datetime.

Parameters:

•Input: value to convert.

Returns: The input converted to a datetime. TO_DATE

to_date ::= TO_DATE

referenced by:

to_guid:

Converts a value into a guid.

Parameters:

•Input: value to convert.

Returns: The input converted to a guid.

Converts a value into a number.

Parameters:

•Input: value to convert.

Returns: The input converted to a number. TO_GUID

to_guid ::= TO_GUID

referenced by:

to_number:

TO_NUMBER

to_number
::= TO_NUMBER

referenced by:

zero_blob:

Generates a blob with 0-byte values.

Parameters:

•Length: Produce a blob with this length in terms of bytes.

Returns: A blob with 0-byte values. ZERO_BLOB

zero_blob
::= ZERO_BLOB

referenced by:

now:

The time of the system clock in local time at the device where Invantive UniversalSQL runs.

Returns: current date/time.

NOW GETDATE SYSDATETIME parenthesisOpen parenthesisClose SYSDATE

now ::= ( NOW | GETDATE | SYSDATETIME ) parenthesisOpen parenthesisClose
| SYSDATE

referenced by:

utc:

UTC_DATE parenthesisOpen parenthesisClose GETUTCDATE NOWUTC parenthesisOpen parenthesisClose SYSDATEUTC

utc ::= UTC_DATE ( parenthesisOpen parenthesisClose )?
| ( GETUTCDATE | NOWUTC ) parenthesisOpen parenthesisClose
| SYSDATEUTC

referenced by:

fullTableIdentifier:

catalogIdentifier DOT schemaIdentifier DOT tableIdentifier

fullTableIdentifier
::= ( catalogIdentifier DOT ( schemaIdentifier? DOT )? )? tableIdentifier

referenced by:

•tableOrFunctionSpec

•tableSpec

catalogIdentifier:

identifier

catalogIdentifier
::= identifier

referenced by:

•fullTableIdentifier

schemaIdentifier:

identifier

schemaIdentifier
::= identifier

referenced by:

•fullTableIdentifier

tableIdentifier:

identifier

tableIdentifier
::= identifier

referenced by:

•fullTableIdentifier

fieldIdentifier:

alias DOT identifier

fieldIdentifier
::= ( alias DOT )? identifier

referenced by:

attributeIdentifier:

identifierWithMinus keywordsAsIdentifierOrAlias

attributeIdentifier
::= identifierWithMinus
| keywordsAsIdentifierOrAlias

referenced by:

•setIdentifier

identifierWithMinus:

identifier MINUS identifier INT_OR_DECIMAL_C ESCAPED_IDENTIFIER

identifierWithMinus
::= ESCAPED_IDENTIFIER
| identifier ( MINUS ( identifier | INT_OR_DECIMAL_C )? )*

referenced by:

•attributeIdentifier

identifier:

ESCAPED_IDENTIFIER IDENTIFIER keywordsAsIdentifierOrAlias

identifier
::= ESCAPED_IDENTIFIER
| IDENTIFIER
| keywordsAsIdentifierOrAlias

referenced by:

•column

•joinSet

•noJoinSet

•partitionSimpleIdentifier

•schemaIdentifier

•tableIdentifier

•xmlTableColumSpec

alias:

ESCAPED_IDENTIFIER IDENTIFIER keywordsAsIdentifierOrAlias

alias ::= ESCAPED_IDENTIFIER
| IDENTIFIER
| keywordsAsIdentifierOrAlias

referenced by:

•aliased

•allColumnsSpecId

•fieldIdentifier

keywordsAsIdentifierOrAlias:

ABS ACOS ADD_MONTHS ANONYMIZE APPROACH ASC ASCII ASIN ADD_MONTHS ATAN ATAN2 ATTACH AUTO AVG BEGIN BIT BIT_LENGTH BY CACHE CAMEL CASE CEIL CHAR CHR COALESCE COMMIT COMPRESS CODE COLUMN COLUMNS CONTRACT COPY COS COUNT COVFEFIFY CROSS CSVTABLE DATA DATE DATEADD DATEPART DATETIME DATETIMEOFFSET DATE_CEIL DATE_FLOOR DATE_ROUND DATE_TRUNC DEC DELIMITER DENSE_RANK DESC DOWNLOAD DOUBLE DROPPABLE DROPPED ELSE END EXP FEED FLOOR FORCE FORWARDED FRESH FROM_UNIXTIME FULL GETDATE GETUTCDATE GROUP HTTPGET HTTPGET_TEXT HTTPPOST IDENTIFIED IMAGE INITCAP INCOMING INTEGER INTERSECT INTERVAL JOIN_SET BASE64_DECODE BASE64_ENCODE JSONDECODE JSONENCODE LABEL LEFT LENGTH LEVENSHTEIN LICENSE LIMIT LINES LISTAGG LOAD LOGICAL LONGTEXT LOWER LOW_COST LPAD LTRIM MAINTAIN MAX MD5 MESSAGES METADATA MEDIUMTEXT MIN MINUS_C MOD MODEL MONEY MY NAME NEWID NO_JOIN_SET NORMALIZE NOWUTC NUMBER NUMBER_TO_SPEECH NVL OBSOLETE OCTET_LENGTH ODS ONCE OUTER OVERALL PARALLEL PASSING PARTITION PATH PERSISTENT POSITION POSTFIX POWER PREFIX PRODUCT PURGE QUOTE_IDENT QUOTE_LITERAL QUOTE_NULLABLE RAISE_ERROR RAND RANK RANDOM RANDOM_BLOB READY RECYCLEBIN REFRESH REGEXP_INSTR REGEXP_REPLACE REGEXP_SUBSTR REMAINDER REPEAT RESULT_SET_NAME RETENTION REVERSE RIGHT ROLLBACK ROUND ROW ROW_NUMBER RPAD RTRIM SAMPLE SERIAL SIN SKIP_ SOUNDEX SQRT STATE STDDEV SUM SYSDATETIME SYSDATEUTC SYS_CONTEXT TABLES TAN TEXT THEN TIME TIMESTAMP TINYTEXT TO TOKEN TOP TO_BINARY TO_CHAR TO_DATE TO_GUID TO_HEX TO_NUMBER TRANSACTION TRANSLATE TRANSLATE_RESOURCES TRICKLE TRIM TRUNC UNCOMPRESS UNION UNIQUEIDENTIFIER UNISTR UNIX_TIMESTAMP UNKNOWN UNZIP UPDATE UPGRADE UPPER URLDECODE URLENCODE USE USER UTC UTC_DATE VERSION VERSIONS WHEN XML XMLCOMMENT XMLDECODE XMLELEMENT XMLENCODE XMLFORMAT XMLTABLE XMLTRANSFORM XMLTYPE YEAR ZERO_BLOB ZIP LOG LN MICROSECOND MILLISECOND SECOND MINUTE HOUR INSTR DAY DAYOFWEEK DAYOFYEAR MONTH QUARTER YEAR CONCAT WITH EQUAL SUBSTR

referenced by:

•alias

•attributeIdentifier

•identifier

constant:

A constant value with associated data type. The null value is typically associated with the null data type.

stringConstant numericConstant booleanConstant intervalConstant null

constant ::= stringConstant
| numericConstant
| booleanConstant
| intervalConstant
| null

referenced by:

•allColumnsSpecColumnNamePostfix

•pSqlItemDeclaration

stringConstant:

A constant text value with varchar2 data type.

STRING_C

stringConstant
::= STRING_C

referenced by:

•allColumnsSpecColumnNamePrefix

•allColumnsSpecLabelPostfix

•allColumnsSpecLabelPrefix

•alterPersistentCacheDownloadStatement

•alterPersistentCacheDropStatement

•labeled

intervalConstant:

A constant interval value, reflecting the time span between two dates. The string constant consists of an integer number and unit of time, taken from the following list:

•Millisecond,

•second,

•minute,

•hour,

•day,

•week, and

•year.

The units may be postfixed with an 's' without changing meaning, like 'years'.

Valid interval values are for example: "5 seconds", "20 hours" and "1 year". There is no support for combined intervals such as "30 minutes and 30 seconds".

INTERVAL stringConstant

intervalConstant
::= INTERVAL stringConstant

referenced by:

•alterPersistentCacheDownloadStatement

•httpDiskCache

•httpMemoryCache

•ods

numericConstant:

A constant numeric value with numeric data type.

INT_OR_DECIMAL_C E_NOTATION_C

numericConstant
::= INT_OR_DECIMAL_C
| E_NOTATION_C

referenced by:

•alterPersistentCachePartitionRefreshStatement

•alterPersistentCacheRefreshStatement

•alterPersistentCacheTableRefreshStatement

•csvTableColumSpec

•csvTableOptions

•joinSet

•limitClause

•pSqlForNumberLoopStatement

•partitionIdentifier

•partitionSimpleIdentifier

•topClause

booleanConstant:

true false

booleanConstant
::= true
| false

referenced by:

•httpDiskCache

•httpMemoryCache

•ods

null:

The "unknown" value null.

NULL

null ::= NULL

referenced by:

pSqlBlock:

A PSQL block is a structure to define procedural logic. It can contain both procedural logic as well as SQL statements like "select".

pSqlDeclareSection pSqlBody

pSqlBlock
::= pSqlDeclareSection? pSqlBody

referenced by:

•pSqlBlockOrStatement

pSqlDeclareSection:

A PSQL declare section defines one or more local variables, which are available in the block and nested blocks.

DECLARE pSqlDeclaration

pSqlDeclareSection
::= DECLARE pSqlDeclaration+

referenced by:

•pSqlBlock

pSqlDeclaration:

pSqlItemDeclaration

pSqlDeclaration
::= pSqlItemDeclaration

referenced by:

•pSqlDeclareSection

pSqlItemDeclaration:

An item declaration defines one named variable, based upon data type. The initial value can be added as a constant.

variableName dataType ASSIGNMENT_OPERATOR constant BATCHSEPARATOR

pSqlItemDeclaration
::= variableName dataType ( ASSIGNMENT_OPERATOR constant )? BATCHSEPARATOR

referenced by:

•pSqlDeclaration

pSqlBody:

A PSQL body contains the procedural logic as well as SQL statements. Variables must have been declared beforehand.

BEGIN pSqlStatement END BATCHSEPARATOR

pSqlBody ::= BEGIN pSqlStatement+ END BATCHSEPARATOR

referenced by:

•pSqlBlock

pSqlStatement:

A number of basic PSQL statements are available.

pSqlAssignmentStatement pSqlExecuteImmediateStatement pSqlIfStatement pSqlLoopStatement pSqlNullStatement pSqlBlock sqlStatement BATCHSEPARATOR

referenced by:

•pSqlBlockOrStatement

•pSqlBody

•sqlOrPSqlStatement

pSqlBlockOrStatement:

A PSQL block or statement defines a procedural step or a SQL statement to be executed.

pSqlBlock pSqlStatement

pSqlBlockOrStatement
::= pSqlBlock
| pSqlStatement

referenced by:

•pSqlBlockOrStatements

pSqlBlockOrStatements:

pSqlBlockOrStatement

pSqlBlockOrStatements
::= pSqlBlockOrStatement+

referenced by:

•pSqlElsIfExpression

•pSqlForNumberLoopStatement

•pSqlForRecordLoopStatement

•pSqlIfStatement

•pSqlWhileLoopStatement

pSqlNullStatement:

The null-statement is a NOP-statement (No Operator). The use of the null-statement is necessary when a PSQL statement is needed, but no activity needs to be performed such as with an if statement. The null-statement also makes explicit that a developer has considered the actions needed and found that no action applies to a specific scenario. This leads to improved code documentation.

NULL BATCHSEPARATOR

pSqlNullStatement
::= NULL BATCHSEPARATOR

referenced by:

pSqlAssignmentStatement
::= variableName ASSIGNMENT_OPERATOR expression BATCHSEPARATOR

pSqlAssignmentStatement:

The assignment statement assign a new value to a variable. To assign the results of a SQL query to a value, use a select ... into ... statement.

variableName ASSIGNMENT_OPERATOR expression BATCHSEPARATOR

referenced by:

pSqlExecuteImmediateStatement:

The execute immediate PSQL statement enables the use of SQL statements that are compiled at runtime. For instance dynamic DDL statements can not always be executed on compiled time and the execute immediate enables these.

EXECUTE IMMEDIATE expression BATCHSEPARATOR

pSqlExecuteImmediateStatement
::= EXECUTE IMMEDIATE expression BATCHSEPARATOR

referenced by:

pSqlIfStatement:

The if-statement performs conditional logic. When the boolean expression after if holds, the PSQL block after the 'then' will be executed. Other branches can be specified using an elsif. Otherwise, and only when specified, the logic after the else is executed.

IF booleanExpression THEN pSqlBlockOrStatements pSqlElsIfExpression ELSE pSqlBlockOrStatements END IF BATCHSEPARATOR

pSqlIfStatement
::= IF booleanExpression THEN pSqlBlockOrStatements pSqlElsIfExpression* ( ELSE pSqlBlockOrStatements )? END IF BATCHSEPARATOR

referenced by:

pSqlElsIfExpression:

ELSIF booleanExpression THEN pSqlBlockOrStatements

pSqlElsIfExpression
::= ELSIF booleanExpression THEN pSqlBlockOrStatements

referenced by:

•pSqlIfStatement

pSqlLoopStatement:

A variety of PSQL statements for loops are available.

pSqlForNumberLoopStatement pSqlForRecordLoopStatement pSqlWhileLoopStatement

pSqlLoopStatement
::= pSqlForNumberLoopStatement
| pSqlForRecordLoopStatement
| pSqlWhileLoopStatement

referenced by: