Please enable JavaScript to view this site.

Navigation: Invantive SQL > Language

Grammar

sqlBatch:

sqlOrPSqlStatement BATCHSEPARATOR BATCHSEPARATOR

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

no references

 

sqlOrPSqlStatement:

sqlStatement pSqlStatement

sqlOrPSqlStatement
        ::= sqlStatement
          | pSqlStatement

referenced by:

sqlBatch

 

sqlStatement:

An Invantive UniversalSQL can retrieve data from 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.

selectStatement insertStatement updateStatement deleteStatement ddlStatement setStatement useStatement transactionStatement executeFileStatement

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

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

arithmeticExpression

createTableStatement

embeddedSelect

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

selectStatement

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

selectStatement

 

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:

selectStatement

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:

joinStatement

uniqueSelectStatement

 

select:

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

executionHints
        ::= EXECUTION_HINT_START ( joinSet | noJoinSet | ods | resultSetName | 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 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:

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

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.

ODS PARENTHESIS_OPEN booleanConstant COMMA intervalConstant PARENTHESIS_CLOSE

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

referenced by:

executionHints

 

resultSetName:

RESULT_SET_NAME PARENTHESIS_OPEN stringConstant PARENTHESIS_CLOSE

resultSetName
        ::= RESULT_SET_NAME ( PARENTHESIS_OPEN stringConstant 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.

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:

executionHints

 

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:

executionHints

 

variableList:

variableName COMMA variableName

variableList
        ::= variableName ( COMMA variableName )?

referenced by:

uniqueSelectStatement

 

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:

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

referenced by:

aggregateFunction

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

TOP numericConstant

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

LIMIT numericConstant

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

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.

fullTableIdentifier distributedAliasDirective

tableSpec
        ::= fullTableIdentifier distributedAliasDirective?

referenced by:

alterPersistentCacheDropStatement

alterPersistentCacheSetTableOptions

alterPersistentCacheTableRefreshStatement

createTableStatement

deleteStatement

dropTableStatement

insertStatement

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.

fullTableIdentifier tableFunctionSpec distributedAliasDirective

tableOrFunctionSpec
        ::= fullTableIdentifier tableFunctionSpec? distributedAliasDirective?

referenced by:

dataSource

 

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:

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

dataSource

 

xmlTablePassing:

PASSING expression

xmlTablePassing
        ::= PASSING expression

referenced by:

xmlTableSpec

 

xmlTableLiteral:

LITERAL expression

xmlTableLiteral
        ::= LITERAL expression

referenced by:

xmlTableSpec

 

xmlTableColumns:

COLUMNS xmlTableColumSpec COMMA

xmlTableColumns
        ::= COLUMNS xmlTableColumSpec ( COMMA xmlTableColumSpec )*

referenced by:

xmlTableSpec

 

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:

dataSource

 

jsonTablePassing:

PASSING expression

jsonTablePassing
        ::= PASSING expression

referenced by:

jsonTableSpec

 

jsonTableLiteral:

LITERAL expression

jsonTableLiteral
        ::= LITERAL expression

referenced by:

jsonTableSpec

 

jsonTableColumns:

COLUMNS jsonTableColumSpec COMMA

jsonTableColumns
        ::= COLUMNS jsonTableColumSpec ( COMMA jsonTableColumSpec )*

referenced by:

jsonTableSpec

 

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:

dataSource

 

csvTableOptions:

ROW DELIMITER stringConstant COLUMN DELIMITER stringConstant SKIP_ LINES numericConstant

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

referenced by:

csvTableSpec

 

csvTableLiteral:

LITERAL expression

csvTableLiteral
        ::= LITERAL expression

referenced by:

csvTableSpec

 

csvTablePassing:

PASSING expression

csvTablePassing
        ::= PASSING expression

referenced by:

csvTableSpec

 

csvTableColumns:

COLUMNS csvTableColumSpec COMMA

csvTableColumns
        ::= COLUMNS csvTableColumSpec ( COMMA csvTableColumSpec )*

referenced by:

csvTableSpec

 

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

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:

csvTableColumSpec

jsonTableColumSpec

pSqlItemDeclaration

xmlTableColumSpec

 

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:

uniqueSelectStatement

 

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:

aggregateFunction

selectStatement

 

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

uniqueSelectStatement

updateStatement

 

joinStatements:

A list of join statement.

joinStatement

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.

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:

joinStatement

 

joinSubCategory:

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

left right full

joinSubCategory
        ::= left
          | right
          | full

referenced by:

joinCategory

 

join:

JOIN

join     ::= JOIN

referenced by:

joinStatement

 

inner:

INNER

inner    ::= INNER

referenced by:

joinCategory

 

outer:

OUTER

outer    ::= OUTER

referenced by:

joinCategory

 

left:

LEFT

left     ::= LEFT

referenced by:

functionExpression

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:

functionExpression

joinSubCategory

 

full:

FULL

full     ::= FULL

referenced by:

joinSubCategory

 

cross:

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

referenced by:

aggregateFunction

 

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:

aggregateFunction

 

min:

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

MIN

min      ::= MIN

referenced by:

aggregateFunction

 

max:

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

MAX

max      ::= MAX

referenced by:

aggregateFunction

 

avg:

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

AVG

avg      ::= AVG

referenced by:

aggregateFunction

 

stddev:

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

STDDEV

stddev   ::= STDDEV

referenced by:

aggregateFunction

 

count:

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

COUNT

count    ::= COUNT

referenced by:

aggregateFunction

 

listagg:

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

LISTAGG

listagg  ::= LISTAGG

referenced by:

aggregateFunction

 

asc:

ASC

asc      ::= ASC

referenced by:

sortDirection

 

desc:

DESC

desc     ::= DESC

referenced by:

sortDirection

 

joinConditions:

ON booleanExpression

joinConditions
        ::= ON booleanExpression

referenced by:

joinStatement

 

selectList:

selectPart COMMA

selectList
        ::= selectPart ( COMMA selectPart )*

referenced by:

uniqueSelectStatement

 

selectPart:

part aliased labeled

selectPart
        ::= part aliased? labeled?

referenced by:

selectList

 

aliased:

AS alias

aliased  ::= AS? alias

referenced by:

dataSource

selectPart

 

labeled:

LABEL stringConstant

labeled  ::= LABEL stringConstant

referenced by:

selectPart

 

part:

expression aggregateFunction allColumnsSpec

part     ::= expression
          | aggregateFunction
          | allColumnsSpec

referenced by:

aggregateFunction

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

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

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

referenced by:

part

 

allColumnsSpecId:

alias DOT ASTERIX

allColumnsSpecId
        ::= ( alias DOT )? ASTERIX

referenced by:

allColumnsSpec

 

allColumnsSpecColumnNamePrefix:

PREFIX WITH stringConstant

allColumnsSpecColumnNamePrefix
        ::= PREFIX WITH stringConstant

referenced by:

allColumnsSpec

 

allColumnsSpecColumnNamePostfix:

POSTFIX WITH stringConstant

allColumnsSpecColumnNamePostfix
        ::= POSTFIX WITH stringConstant

referenced by:

allColumnsSpec

 

allColumnsSpecLabelPrefix:

LABEL PREFIX WITH stringConstant

allColumnsSpecLabelPrefix
        ::= LABEL PREFIX WITH stringConstant

referenced by:

allColumnsSpec

 

allColumnsSpecLabelPostfix:

LABEL POSTFIX WITH stringConstant

allColumnsSpecLabelPostfix
        ::= LABEL POSTFIX WITH stringConstant

referenced by:

allColumnsSpec

 

ddlStatement:

createTableStatement dropTableStatement alterPersistentCacheStatement

ddlStatement
        ::= createTableStatement
          | dropTableStatement
          | alterPersistentCacheStatement

referenced by:

sqlStatement

 

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

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

alterPersistentCacheStatement

 

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:

alterPersistentCacheStatement

 

alterPersistentCacheRefreshStatement:

ALTER PERSISTENT CACHE FORCE REFRESH DATA_CONTAINER dataContainerAlias PARALLEL numericConstant

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

referenced by:

alterPersistentCacheStatement

 

alterPersistentCacheLoadStatement:

ALTER PERSISTENT CACHE LOAD

alterPersistentCacheLoadStatement
        ::= ALTER PERSISTENT CACHE LOAD

referenced by:

alterPersistentCacheStatement

 

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:

alterPersistentCacheStatement

 

alterPersistentCachePartitionRefreshStatement:

ALTER PERSISTENT CACHE PARTITION partitionIdentifier FORCE REFRESH PARALLEL numericConstant

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

referenced by:

alterPersistentCacheStatement

 

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:

alterPersistentCacheStatement

 

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

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:

alterPersistentCacheStatement

 

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

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:

alterPersistentCacheSetStatement

 

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:

sqlStatement

 

setIdentifier:

attributeIdentifier distributedAliasDirective

setIdentifier
        ::= attributeIdentifier distributedAliasDirective?

referenced by:

setStatement

 

transactionStatement:

beginTransactionStatement rollbackTransactionStatement commitTransactionStatement

transactionStatement
        ::= beginTransactionStatement
          | rollbackTransactionStatement
          | commitTransactionStatement

referenced by:

sqlStatement

 

executeFileStatement:

FILE_PATH

executeFileStatement
        ::= FILE_PATH

referenced by:

sqlStatement

 

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:

sqlStatement

 

partitionIdentifiersList:

partitionIdentifierWithAlias COMMA

partitionIdentifiersList
        ::= partitionIdentifierWithAlias ( COMMA partitionIdentifierWithAlias )*

referenced by:

useStatement

 

partitionIdentifier:

parameterExpression numericConstant identifier ALL DEFAULT

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

referenced by:

alterPersistentCacheDropStatement

alterPersistentCachePartitionRefreshStatement

alterPersistentCacheSetTableOptions

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:

sqlStatement

 

valuesExpression:

values_ insertValues

valuesExpression
        ::= values_ insertValues

referenced by:

insertStatement

 

bulk:

BULK

bulk     ::= BULK

referenced by:

insertStatement

 

into:

INTO

into     ::= INTO

referenced by:

insertStatement

 

insert:

INSERT

insert   ::= INSERT

referenced by:

insertStatement

 

values_:

VALUES

values_  ::= VALUES

referenced by:

valuesExpression

 

insertFieldList:

parenthesisOpen columnList parenthesisClose

insertFieldList
        ::= parenthesisOpen columnList parenthesisClose

referenced by:

insertStatement

 

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:

insertStatement

 

attachToClause:

ATTACH TO arithmeticExpression

attachToClause
        ::= ATTACH TO arithmeticExpression

referenced by:

insertStatement

 

updateStatement:

UPDATE FROM tableSpec SET updateValuesList whereClause

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

referenced by:

sqlStatement

 

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:

sqlStatement

 

delete:

DELETE

delete   ::= DELETE

referenced by:

deleteStatement

 

expression:

booleanExpression arithmeticExpression

expression
        ::= booleanExpression
          | arithmeticExpression

referenced by:

caseElseExpression

caseWhenThenExpression

csvTableLiteral

csvTablePassing

jsonTableLiteral

jsonTablePassing

pSqlAssignmentStatement

pSqlExecuteImmediateStatement

part

setStatement

tableFunctionSpec

xmlTableLiteral

xmlTablePassing

 

booleanExpression:

not booleanExpression and or booleanExpression parenthesisOpen booleanExpression parenthesisClose predicateExpression true false

booleanExpression
        ::= ( not | booleanExpression ( and | or ) ) booleanExpression
          | parenthesisOpen booleanExpression parenthesisClose
          | predicateExpression
          | true
          | false

referenced by:

booleanExpression

expression

joinConditions

pSqlElsIfExpression

pSqlIfStatement

pSqlWhileLoopStatement

whereClause

 

caseExpression:

case caseWhenThenExpression caseElseExpression end

caseExpression
        ::= case caseWhenThenExpression+ caseElseExpression? end

referenced by:

arithmeticExpression

 

caseWhenThenExpression:

when expression then arithmeticExpression

caseWhenThenExpression
        ::= when expression then arithmeticExpression

referenced by:

caseExpression

 

caseElseExpression:

else expression

caseElseExpression
        ::= else expression

referenced by:

caseExpression

 

parenthesisOpen:

PARENTHESIS_OPEN

parenthesisOpen
        ::= PARENTHESIS_OPEN

referenced by:

aggregateFunction

arithmeticExpression

booleanExpression

csvTableSpec

embeddedSelect

functionExpression

insertFieldList

insertValues

jsonTableSpec

now

predicateExpression

tableFunctionSpec

utc

xmlTableSpec

 

parenthesisClose:

PARENTHESIS_CLOSE

parenthesisClose
        ::= PARENTHESIS_CLOSE

referenced by:

aggregateFunction

arithmeticExpression

booleanExpression

csvTableSpec

embeddedSelect

functionExpression

insertFieldList

insertValues

jsonTableSpec

now

predicateExpression

tableFunctionSpec

utc

xmlTableSpec

 

case:

CASE

case     ::= CASE

referenced by:

caseExpression

 

when:

WHEN

when     ::= WHEN

referenced by:

caseWhenThenExpression

 

then:

THEN

then     ::= THEN

referenced by:

caseWhenThenExpression

 

else:

ELSE

else     ::= ELSE

referenced by:

caseElseExpression

 

end:

END

end      ::= END

referenced by:

caseExpression

 

not:

NOT

not      ::= NOT

referenced by:

booleanExpression

isLikeComparingExpression

isNullComparingExpression

predicateExpression

 

is:

IS

is       ::= IS

referenced by:

isNullComparingExpression

 

are:

ARE

are      ::= ARE

referenced by:

isEqualComparingExpression

 

and:

AND

and      ::= AND

referenced by:

booleanExpression

predicateExpression

 

or:

OR

or       ::= OR

referenced by:

booleanExpression

 

true:

TRUE

true     ::= TRUE

referenced by:

booleanConstant

booleanExpression

 

false:

FALSE

false    ::= FALSE

referenced by:

booleanConstant

booleanExpression

 

predicateExpression:

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

predicateExpression
        ::= arithmeticExpression ( ( gt | ge | lt | le | eq | neq ) arithmeticExpression | not? ( between arithmeticExpression and arithmeticExpression | in_ parenthesisOpen ( arithmeticExpression ( COMMA arithmeticExpression )* | inSelectStatement ) parenthesisClose ) | isNullComparingExpression | isLikeComparingExpression | isEqualComparingExpression )

referenced by:

booleanExpression

 

parameterExpression:

COLON identifier

parameterExpression
        ::= COLON identifier

referenced by:

arithmeticExpression

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

referenced by:

predicateExpression

 

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

referenced by:

predicateExpression

 

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

referenced by:

predicateExpression

 

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

referenced by:

predicateExpression

 

eq:

EQ

eq       ::= EQ

referenced by:

predicateExpression

 

neq:

NEQ

neq      ::= NEQ

referenced by:

predicateExpression

 

like:

LIKE

like     ::= LIKE

referenced by:

isLikeComparingExpression

 

between:

BETWEEN

between  ::= BETWEEN

referenced by:

predicateExpression

 

in_:

IN

in_      ::= IN

referenced by:

predicateExpression

 

isNullComparingExpression:

is not NULL

isNullComparingExpression
        ::= is not? NULL

referenced by:

predicateExpression

 

isEqualComparingExpression:

are EQUAL

isEqualComparingExpression
        ::= are? EQUAL

referenced by:

predicateExpression

 

isLikeComparingExpression:

not like arithmeticExpression

isLikeComparingExpression
        ::= not? like arithmeticExpression

referenced by:

predicateExpression

 

arithmeticExpression:

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

arithmeticExpression
        ::= ( minus | plus | arithmeticExpression ( times | divide | plus | minus | concat ) ) arithmeticExpression
          | parenthesisOpen ( arithmeticExpression | selectStatement ) parenthesisClose
          | functionExpression
          | parameterExpression
          | caseExpression
          | fieldIdentifier
          | constant

referenced by:

aggregateFunction

arithmeticExpression

arithmeticExpressionList

attachToClause

caseWhenThenExpression

expression

identifiedByClause

insertValuesList

isLikeComparingExpression

predicateExpression

updateValue

 

arithmeticExpressionList:

arithmeticExpression list

arithmeticExpressionList
        ::= arithmeticExpression ( list arithmeticExpression )*

referenced by:

aggregateFunction

functionExpression

 

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

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:

arithmeticExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

raise_error:

RAISE_ERROR

raise_error
        ::= RAISE_ERROR

referenced by:

functionExpression

 

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:

functionExpression

 

concat:

Concatenate the left and right values together as a text.

CONCAT_OP

concat   ::= CONCAT_OP

referenced by:

arithmeticExpression

 

concat_func:

Concatenate a list of values together as a text.

CONCAT

concat_func
        ::= CONCAT

referenced by:

functionExpression

 

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:

functionExpression

 

covfefify:

COVFEFIFY

covfefify
        ::= COVFEFIFY

referenced by:

functionExpression

 

compress:

COMPRESS

compress ::= COMPRESS

referenced by:

functionExpression

 

uncompress:

UNCOMPRESS

uncompress
        ::= UNCOMPRESS

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

date_ceil:

DATE_CEIL

date_ceil
        ::= DATE_CEIL

referenced by:

functionExpression

 

date_floor:

DATE_FLOOR

date_floor
        ::= DATE_FLOOR

referenced by:

functionExpression

 

date_round:

DATE_ROUND

date_round
        ::= DATE_ROUND

referenced by:

functionExpression

 

date_trunc:

DATE_TRUNC

date_trunc
        ::= DATE_TRUNC

referenced by:

functionExpression

 

day:

Collect the day from a date.

Parameters:

Input: A dateTime.

Returns: The day as an integer. DAY

day      ::= DAY

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

dense_rank:

DENSE_RANK

dense_rank
        ::= DENSE_RANK

referenced by:

functionExpression

 

double_metaphone:

DOUBLE_METAPHONE

double_metaphone
        ::= DOUBLE_METAPHONE

referenced by:

functionExpression

 

double_metaphone_alt:

DOUBLE_METAPHONE_ALT

double_metaphone_alt
        ::= DOUBLE_METAPHONE_ALT

referenced by:

functionExpression

 

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:

arithmeticExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

hour:

Collect the hour from a date.

Parameters:

Input: A dateTime.

Returns: The hour as an integer. HOUR

hour     ::= HOUR

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

jsondecode:

JSONDECODE

jsondecode
        ::= JSONDECODE

referenced by:

functionExpression

 

jsonencode:

JSONENCODE

jsonencode
        ::= JSONENCODE

referenced by:

functionExpression

 

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:

functionExpression

 

levenshtein:

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

LEVENSHTEIN

levenshtein
        ::= LEVENSHTEIN

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

metaphone3:

METAPHONE3

metaphone3
        ::= METAPHONE3

referenced by:

functionExpression

 

metaphone3_alt:

METAPHONE3_ALT

metaphone3_alt
        ::= METAPHONE3_ALT

referenced by:

functionExpression

 

mod:

Get the remainder of a divide calculation.

Parameters:

dividend: a number.

divider: a number.

Returns: The remainder. MOD

mod      ::= MOD

referenced by:

functionExpression

 

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:

arithmeticExpression

 

minute:

Collect the minute from a date.

Parameters:

Input: A dateTime.

Returns: The minute as an integer. MINUTE

minute   ::= MINUTE

referenced by:

functionExpression

 

month:

Collect the month from a date.

Parameters:

Input: A dateTime.

Returns: The month as an integer. MONTH

month    ::= MONTH

referenced by:

functionExpression

 

newid:

Creates a new Guid id.

Returns: The new Guid id.

NEWID

newid    ::= NEWID

referenced by:

functionExpression

 

nvl:

Coalesce all values together.

Returns: All values coalesced together.

NVL

nvl      ::= NVL

referenced by:

functionExpression

 

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:

arithmeticExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

rand:

RAND

rand     ::= RAND

referenced by:

functionExpression

 

rank:

RANK

rank     ::= RANK

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

row_number:

ROW_NUMBER

row_number
        ::= ROW_NUMBER

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

microsecond:

Collect the microsecond from a date.

Parameters:

Input: A dateTime.

Returns: The microsecond as an integer. MICROSECOND

microsecond
        ::= MICROSECOND

referenced by:

functionExpression

 

millisecond:

Collect the millisecond from a date.

Parameters:

Input: A dateTime.

Returns: The millisecond as an integer. MILLISECOND

millisecond
        ::= MILLISECOND

referenced by:

functionExpression

 

number_to_speech:

NUMBER_TO_SPEECH

number_to_speech
        ::= NUMBER_TO_SPEECH

referenced by:

functionExpression

 

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:

functionExpression

 

second:

Collect the second from a date.

Parameters:

Input: A dateTime.

Returns: The second as an integer. SECOND

second   ::= SECOND

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

arithmeticExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

to_hex:

TO_HEX

to_hex   ::= TO_HEX

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

urldecode:

Decodes a url.

Parameters:

Url: url to decode.

Returns: The decoded url. URLDECODE

urldecode
        ::= URLDECODE

referenced by:

functionExpression

 

urlencode:

Encodes a url.

Parameters:

Url: url to encode.

Returns: The encoded url. URLENCODE

urlencode
        ::= URLENCODE

referenced by:

functionExpression

 

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:

functionExpression

 

unzip:

UNZIP

unzip    ::= UNZIP

referenced by:

functionExpression

 

zip:

ZIP

zip      ::= ZIP

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

xmlelement:

XMLELEMENT

xmlelement
        ::= XMLELEMENT

referenced by:

functionExpression

 

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:

functionExpression

 

xmlformat:

Pretty-print xml text.

Parameters:

Xml: xml to pretty-print.

Returns: The pretty-printed XML text. XMLFORMAT

xmlformat
        ::= XMLFORMAT

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

httppost:

HTTPPOST

httppost ::= HTTPPOST

referenced by:

functionExpression

 

quarter:

Collect the quarter from a date.

Parameters:

Input: A dateTime.

Returns: The quarter as an integer. QUARTER

quarter  ::= QUARTER

referenced by:

functionExpression

 

quote_ident:

QUOTE_IDENT

quote_ident
        ::= QUOTE_IDENT

referenced by:

functionExpression

 

quote_literal:

QUOTE_LITERAL

quote_literal
        ::= QUOTE_LITERAL

referenced by:

functionExpression

 

quote_nullable:

QUOTE_NULLABLE

quote_nullable
        ::= QUOTE_NULLABLE

referenced by:

functionExpression

 

user:

Gets the user log on code.

Returns: The user log on code.

USER

user     ::= USER

referenced by:

functionExpression

 

year:

Collect the year from a date.

Parameters:

Input: A dateTime.

Returns: The year as an integer. YEAR

year     ::= YEAR

referenced by:

functionExpression

 

to_binary:

TO_BINARY

to_binary
        ::= TO_BINARY

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

to_number:

TO_NUMBER

to_number
        ::= TO_NUMBER

referenced by:

functionExpression

 

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:

functionExpression

 

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:

functionExpression

 

utc:

UTC_DATE parenthesisOpen parenthesisClose GETUTCDATE NOWUTC parenthesisOpen parenthesisClose SYSDATEUTC

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

referenced by:

functionExpression

 

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:

arithmeticExpression

 

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:

catalogIdentifier

column

csvTableColumSpec

dataContainerAlias

fieldIdentifier

identifierWithMinus

joinSet

jsonTableColumSpec

noJoinSet

parameterExpression

partitionIdentifier

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

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:

arithmeticExpression

pSqlItemDeclaration

 

stringConstant:

A constant text value with varchar2 data type.

STRING_C

stringConstant
        ::= STRING_C

referenced by:

allColumnsSpecColumnNamePostfix

allColumnsSpecColumnNamePrefix

allColumnsSpecLabelPostfix

allColumnsSpecLabelPrefix

alterPersistentCacheDownloadStatement

alterPersistentCacheDropStatement

alterPersistentCacheSetStatement

alterPersistentCacheSetTableOptions

constant

csvTableOptions

intervalConstant

jsonTableColumSpec

jsonTableSpec

labeled

resultSetName

xmlTableColumSpec

xmlTableSpec

 

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:

constant

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:

alterPersistentCacheDownloadStatement

alterPersistentCachePartitionRefreshStatement

alterPersistentCacheRefreshStatement

alterPersistentCacheSetStatement

alterPersistentCacheTableRefreshStatement

constant

csvTableColumSpec

csvTableOptions

joinSet

limitClause

pSqlForNumberLoopStatement

partitionIdentifier

partitionSimpleIdentifier

topClause

 

booleanConstant:

true false

booleanConstant
        ::= true
          | false

referenced by:

alterPersistentCacheSetStatement

alterPersistentCacheSetTableOptions

constant

httpDiskCache

httpMemoryCache

ods

 

null:

The "unknown" value null.

NULL

null     ::= NULL

referenced by:

constant

jsonTableSpec

xmlTableSpec

 

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

pSqlStatement

 

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

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

pSqlStatement

 

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

pSqlAssignmentStatement
        ::= variableName ASSIGNMENT_OPERATOR expression BATCHSEPARATOR

referenced by:

pSqlStatement

 

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:

pSqlStatement

 

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:

pSqlStatement

 

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:

pSqlStatement

 

pSqlForNumberLoopStatement:

This PSQL integer loop statement iterates over a range of integer values, executing PSQL statements for each iterated value. The iterations goes from the first value to the last value in increments of 1. The iterations go backward in decrements of 1 when 'reverse' is specified.

FOR variableName IN REVERSE numericConstant variableName DOT DOT numericConstant variableName LOOP pSqlBlockOrStatements END LOOP BATCHSEPARATOR

pSqlForNumberLoopStatement
        ::= FOR variableName IN REVERSE? ( numericConstant | variableName ) DOT DOT ( numericConstant | variableName ) LOOP pSqlBlockOrStatements END LOOP BATCHSEPARATOR

referenced by:

pSqlLoopStatement

 

pSqlForRecordLoopStatement:

This PSQL result set loop statement iterates over a result set returned by an Invantive UniversalSQL query. The PSQL statements are executed for each record. The record's specific values can be retrieved using the variable.

FOR variableName IN PARENTHESIS_OPEN selectStatement PARENTHESIS_CLOSE LOOP pSqlBlockOrStatements END LOOP BATCHSEPARATOR

pSqlForRecordLoopStatement
        ::= FOR variableName IN PARENTHESIS_OPEN selectStatement PARENTHESIS_CLOSE LOOP pSqlBlockOrStatements END LOOP BATCHSEPARATOR

referenced by:

pSqlLoopStatement

 

pSqlWhileLoopStatement:

This PSQL while loop statement executes PSQL statements as long as the specified boolean condition evaluates to true at loop end.

WHILE booleanExpression LOOP pSqlBlockOrStatements END LOOP BATCHSEPARATOR

pSqlWhileLoopStatement
        ::= WHILE booleanExpression LOOP pSqlBlockOrStatements END LOOP BATCHSEPARATOR

referenced by:

pSqlLoopStatement

 

variableName:

IDENTIFIER

variableName
        ::= IDENTIFIER

referenced by:

pSqlAssignmentStatement

pSqlForNumberLoopStatement

pSqlForRecordLoopStatement

pSqlItemDeclaration

variableList