Grammar |
sqlOrPSqlStatement BATCHSEPARATOR BATCHSEPARATOR
sqlBatch ::= sqlOrPSqlStatement ( BATCHSEPARATOR sqlOrPSqlStatement )* BATCHSEPARATOR?
no references
sqlStatement pSqlStatement
sqlOrPSqlStatement
::= sqlStatement
| pSqlStatement
referenced by:
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:
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:
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:
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:
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:
A data source can be a table, a table with parameters or a nested select (an 'inline view').
tableOrFunctionSpec embeddedSelect xmlTableSpec csvTableSpec jsonTableSpec aliased
dataSource
::= ( tableOrFunctionSpec | embeddedSelect | xmlTableSpec | csvTableSpec | jsonTableSpec ) aliased?
referenced by:
SELECT
referenced by:
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:
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:
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:
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:
RESULT_SET_NAME PARENTHESIS_OPEN stringConstant PARENTHESIS_CLOSE
resultSetName
::= RESULT_SET_NAME ( PARENTHESIS_OPEN stringConstant PARENTHESIS_CLOSE )?
referenced by:
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:
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:
variableName COMMA variableName
variableList
::= variableName ( COMMA variableName )?
referenced by:
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
referenced by:
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
referenced by:
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:
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:
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:
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
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:
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:
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:
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
XMLTABLE parenthesisOpen stringConstant null xmlTablePassing xmlTableLiteral xmlTableColumns parenthesisClose
xmlTableSpec
::= XMLTABLE parenthesisOpen ( stringConstant | null ) ( xmlTablePassing | xmlTableLiteral ) xmlTableColumns parenthesisClose
referenced by:
PASSING expression
xmlTablePassing
::= PASSING expression
referenced by:
LITERAL expression
xmlTableLiteral
::= LITERAL expression
referenced by:
COLUMNS xmlTableColumSpec COMMA
xmlTableColumns
::= COLUMNS xmlTableColumSpec ( COMMA xmlTableColumSpec )*
referenced by:
identifier dataType PATH stringConstant
xmlTableColumSpec
::= identifier dataType PATH stringConstant
referenced by:
JSONTABLE parenthesisOpen stringConstant null jsonTablePassing jsonTableLiteral jsonTableColumns parenthesisClose
jsonTableSpec
::= JSONTABLE parenthesisOpen ( stringConstant | null ) ( jsonTablePassing | jsonTableLiteral ) jsonTableColumns parenthesisClose
referenced by:
PASSING expression
jsonTablePassing
::= PASSING expression
referenced by:
LITERAL expression
jsonTableLiteral
::= LITERAL expression
referenced by:
COLUMNS jsonTableColumSpec COMMA
jsonTableColumns
::= COLUMNS jsonTableColumSpec ( COMMA jsonTableColumSpec )*
referenced by:
identifier dataType PATH stringConstant
jsonTableColumSpec
::= identifier dataType PATH stringConstant
referenced by:
CSVTABLE parenthesisOpen csvTablePassing csvTableLiteral csvTableOptions csvTableColumns parenthesisClose
csvTableSpec
::= CSVTABLE parenthesisOpen ( csvTablePassing | csvTableLiteral ) csvTableOptions csvTableColumns parenthesisClose
referenced by:
ROW DELIMITER stringConstant COLUMN DELIMITER stringConstant SKIP_ LINES numericConstant
csvTableOptions
::= ( ROW DELIMITER stringConstant )? ( COLUMN DELIMITER stringConstant )? ( SKIP_ LINES numericConstant )?
referenced by:
LITERAL expression
csvTableLiteral
::= LITERAL expression
referenced by:
PASSING expression
csvTablePassing
::= PASSING expression
referenced by:
COLUMNS csvTableColumSpec COMMA
csvTableColumns
::= COLUMNS csvTableColumSpec ( COMMA csvTableColumSpec )*
referenced by:
identifier dataType POSITION numericConstant
csvTableColumSpec
::= identifier dataType POSITION numericConstant
referenced by:
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:
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:
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:
A sort direction can be either 'asc' for 'ascending' (the default) or 'desc' for 'descending'.
asc desc
sortDirection
::= asc
| desc
referenced by:
A comma-separated list of columns.
column COMMA
columnList
::= column ( COMMA column )*
referenced by:
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:
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:
A list of join statement.
joinStatement
joinStatements
::= joinStatement+
referenced by:
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:
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:
The join sub-category refines the join category. Please see 'joinCategory' for an explanation.
left right full
joinSubCategory
::= left
| right
| full
referenced by:
JOIN
referenced by:
INNER
referenced by:
OUTER
referenced by:
LEFT
referenced by:
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
referenced by:
FULL
referenced by:
CROSS
referenced by:
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
referenced by:
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
referenced by:
Group function to find the minimum value from a group of numerical values.
MIN
referenced by:
Group function to find the maximum value from a group of numerical values.
MAX
referenced by:
Group function to find the average value from a group of numerical values.
AVG
referenced by:
Group function to find the standard deviation from a group of numerical values.
STDDEV
referenced by:
Group function to find the number of values from a group of values.
COUNT
referenced by:
Group function which concatenates all individual values, separated by the separator when provided and comma plus space otherwise.
LISTAGG
referenced by:
ASC
referenced by:
DESC
referenced by:
ON booleanExpression
joinConditions
::= ON booleanExpression
referenced by:
selectPart COMMA
selectList
::= selectPart ( COMMA selectPart )*
referenced by:
part aliased labeled
selectPart
::= part aliased? labeled?
referenced by:
AS alias
referenced by:
LABEL stringConstant
labeled ::= LABEL stringConstant
referenced by:
expression aggregateFunction allColumnsSpec
part ::= expression
| aggregateFunction
| allColumnsSpec
referenced by:
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
allColumnsSpecId allColumnsSpecColumnNamePrefix allColumnsSpecColumnNamePostfix allColumnsSpecLabelPrefix allColumnsSpecLabelPostfix
allColumnsSpec
::= allColumnsSpecId allColumnsSpecColumnNamePrefix? allColumnsSpecColumnNamePostfix? allColumnsSpecLabelPrefix? allColumnsSpecLabelPostfix?
referenced by:
•part
alias DOT ASTERIX
allColumnsSpecId
::= ( alias DOT )? ASTERIX
referenced by:
allColumnsSpecColumnNamePrefix:
PREFIX WITH stringConstant
allColumnsSpecColumnNamePrefix
::= PREFIX WITH stringConstant
referenced by:
allColumnsSpecColumnNamePostfix:
POSTFIX WITH stringConstant
allColumnsSpecColumnNamePostfix
::= POSTFIX WITH stringConstant
referenced by:
LABEL PREFIX WITH stringConstant
allColumnsSpecLabelPrefix
::= LABEL PREFIX WITH stringConstant
referenced by:
LABEL POSTFIX WITH stringConstant
allColumnsSpecLabelPostfix
::= LABEL POSTFIX WITH stringConstant
referenced by:
createTableStatement dropTableStatement alterPersistentCacheStatement
ddlStatement
::= createTableStatement
| dropTableStatement
| alterPersistentCacheStatement
referenced by:
alterPersistentCacheStatement:
Besides an in-memory cache valid during the duration of a session, Invantive UniversalSQL offers an integrated cache storing data persistently using an on-premise or cloud relation database such as SQL Server or PostgreSQL. When configured, Invantive UniversalSQL first tries to find sufficiently fresh data in the cache. This reduces the number of data loads from slow data containers such as some cloud platforms. In general, the performance increase when the rows can be fully retrieved from a cache is between a factor 25 and 2.500.
Invantive UniversalSQL itself manages the table structure and table contents in the relation database used as a data cache. On initial use just provide an empty database. Invantive UniversalSQL installs a repository consisting of a few tables. The repository tables have names starting with 'dc_'.
For each table partition version, a so-called facts table is created. A facts table contains a full copy of the rows retrieved from the data container. Facts tables have names starting with 'dcd_', followed by a unique hash signaling the table partition version. When necessary, additional database objects are maintained such as indexes to improve performance. As with facts table names, all column names are also hashed based upon an algorithm including the original column name. These facts tables are not intended for direct use using native SQL.
Each facts table has a unique state from the following state, with Ready state signaling the now current version:
•Initializing ('I'): the facts table will be created.
•View creation ('V'): logical views will be created.
•Prepared ('P'): the facts table has been created, but contains yet no rows.
•Seeding ('S'): the facts table is being seeded with the contents of the previously current version.
•Loading ('L'): loading new facts from data container using water shed or another algorithm.
•Ready ('R'): the facts table is available and the current one to be used.
•Obsoleted ('O'): the facts table still exists, but the data has passed it's conservation period. Often a newer version is now current.
•Dropped ('D'): the facts table now longer exist, but the metadata is still present in the repository tables.
The persistent cache in the database can be used with native SQL when extended by Invantive Data Replicator. Invantive Data Replicator can create and maintain a database view (a so-called 'partition view') for the now current version of table partition. Similarly, it can create an 'overall view', showing the rows across all partitions of the now current versions per partition.
The overall views are typically used for consolidation purposes, bringing together data across multiple companies or persons.
alterPersistentCacheSetStatement alterPersistentCacheDownloadStatement alterPersistentCachePurgeStatement alterPersistentCacheRefreshStatement alterPersistentCacheLoadStatement alterPersistentCacheTableRefreshStatement alterPersistentCachePartitionRefreshStatement alterPersistentCacheDropStatement
alterPersistentCacheStatement
::= alterPersistentCacheSetStatement
| alterPersistentCacheDownloadStatement
| alterPersistentCachePurgeStatement
| alterPersistentCacheRefreshStatement
| alterPersistentCacheLoadStatement
| alterPersistentCacheTableRefreshStatement
| alterPersistentCachePartitionRefreshStatement
| alterPersistentCacheDropStatement
referenced by:
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
CREATE orReplace TABLE tableSpec AS selectStatement
createTableStatement
::= CREATE orReplace? TABLE tableSpec AS selectStatement
referenced by:
DROP TABLE tableSpec
dropTableStatement
::= DROP TABLE tableSpec
referenced by:
OR REPLACE
referenced by:
Replaces the value of a provider attribute by a new value.
SET setIdentifier expression
setStatement
::= SET setIdentifier expression
referenced by:
attributeIdentifier distributedAliasDirective
setIdentifier
::= attributeIdentifier distributedAliasDirective?
referenced by:
beginTransactionStatement rollbackTransactionStatement commitTransactionStatement
transactionStatement
::= beginTransactionStatement
| rollbackTransactionStatement
| commitTransactionStatement
referenced by:
FILE_PATH
executeFileStatement
::= FILE_PATH
referenced by:
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:
Forgets all collected transaction data not yet handed over to the backing platform.
ROLLBACK TRANSACTION
rollbackTransactionStatement
::= ROLLBACK TRANSACTION?
referenced by:
Hand over all collected transaction to the backing platform for registration.
COMMIT TRANSACTION
commitTransactionStatement
::= COMMIT TRANSACTION?
referenced by:
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:
partitionIdentifierWithAlias COMMA
partitionIdentifiersList
::= partitionIdentifierWithAlias ( COMMA partitionIdentifierWithAlias )*
referenced by:
parameterExpression numericConstant identifier ALL DEFAULT
partitionIdentifier
::= parameterExpression
| numericConstant
| identifier
| ALL
| DEFAULT
referenced by:
•alterPersistentCacheDropStatement
•alterPersistentCachePartitionRefreshStatement
•alterPersistentCacheSetTableOptions
•alterPersistentCacheTableRefreshStatement
partitionIdentifier distributedAliasDirective
partitionIdentifierWithAlias
::= partitionIdentifier distributedAliasDirective?
referenced by:
numericConstant identifier
partitionSimpleIdentifier
::= numericConstant
| identifier
referenced by:
•alterPersistentCacheDownloadStatement
bulk insert into tableSpec insertFieldList valuesExpression insertFieldList selectStatement identifiedByClause attachToClause
insertStatement
::= bulk? insert into tableSpec ( insertFieldList valuesExpression | insertFieldList? selectStatement ) identifiedByClause? attachToClause?
referenced by:
values_ insertValues
valuesExpression
::= values_ insertValues
referenced by:
BULK
referenced by:
INTO
referenced by:
INSERT
referenced by:
VALUES
referenced by:
parenthesisOpen columnList parenthesisClose
insertFieldList
::= parenthesisOpen columnList parenthesisClose
referenced by:
parenthesisOpen insertValuesList parenthesisClose
insertValues
::= parenthesisOpen insertValuesList parenthesisClose
referenced by:
arithmeticExpression COMMA
insertValuesList
::= arithmeticExpression ( COMMA arithmeticExpression )*
referenced by:
IDENTIFIED BY arithmeticExpression
identifiedByClause
::= IDENTIFIED BY arithmeticExpression
referenced by:
ATTACH TO arithmeticExpression
attachToClause
::= ATTACH TO arithmeticExpression
referenced by:
UPDATE FROM tableSpec SET updateValuesList whereClause
updateStatement
::= UPDATE FROM? tableSpec SET updateValuesList whereClause?
referenced by:
updateValue COMMA
updateValuesList
::= updateValue ( COMMA updateValue )*
referenced by:
column EQ arithmeticExpression
updateValue
::= column EQ arithmeticExpression
referenced by:
delete FROM tableSpec whereClause
deleteStatement
::= delete FROM? tableSpec whereClause?
referenced by:
DELETE
referenced by:
booleanExpression arithmeticExpression
expression
::= booleanExpression
| arithmeticExpression
referenced by:
•pSqlExecuteImmediateStatement
•part
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:
case caseWhenThenExpression caseElseExpression end
caseExpression
::= case caseWhenThenExpression+ caseElseExpression? end
referenced by:
when expression then arithmeticExpression
caseWhenThenExpression
::= when expression then arithmeticExpression
referenced by:
else expression
caseElseExpression
::= else expression
referenced by:
PARENTHESIS_OPEN
parenthesisOpen
::= PARENTHESIS_OPEN
referenced by:
•now
•utc
PARENTHESIS_CLOSE
parenthesisClose
::= PARENTHESIS_CLOSE
referenced by:
•now
•utc
CASE
referenced by:
WHEN
referenced by:
THEN
referenced by:
ELSE
referenced by:
END
referenced by:
NOT
referenced by:
IS
referenced by:
ARE
referenced by:
AND
referenced by:
OR
referenced by:
TRUE
referenced by:
FALSE
referenced by:
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:
COLON identifier
parameterExpression
::= COLON identifier
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
EQ
referenced by:
NEQ
referenced by:
LIKE
referenced by:
BETWEEN
referenced by:
IN
referenced by:
is not NULL
isNullComparingExpression
::= is not? NULL
referenced by:
are EQUAL
isEqualComparingExpression
::= are? EQUAL
referenced by:
not like arithmeticExpression
isLikeComparingExpression
::= not? like arithmeticExpression
referenced by:
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:
arithmeticExpression list
arithmeticExpressionList
::= arithmeticExpression ( list arithmeticExpression )*
referenced by:
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:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
Add an amount of months to a datetime.
Parameters:
•Date: datetime to ass the months to.
•Months: the amount of months to add.
Returns: A new datetime with the amount of months added. ADD_MONTHS
add_months
::= ADD_MONTHS
referenced by:
base64_decode:
Converts the base64_encoded value back to the binairy value as defined on Wikipedia.
•Input: value to convert back to the original.
Returns: The input decoded back to the binairy value. BASE64_DECODE
base64_decode
::= BASE64_DECODE
referenced by:
base64_encode:
Converts a binairy value to base64_encoded characters as defined on Wikipedia.
•Input: value to convert to base64 characters.
Returns: The input encoded to base64 characters. BASE64_ENCODE
base64_encode
::= BASE64_ENCODE
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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:
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
referenced by:
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
referenced by:
RAISE_ERROR
referenced by:
Performs a coalescing operation.
Parameters:
•Left: an object.
•Right: an object.
Returns: the left value if right is empty, otherwise the right value. COALESCE
referenced by:
Concatenate the left and right values together as a text.
CONCAT_OP
referenced by:
Concatenate a list of values together as a text.
CONCAT
concat_func
::= CONCAT
referenced by:
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
referenced by:
COVFEFIFY
referenced by:
COMPRESS
referenced by:
UNCOMPRESS
uncompress
::= UNCOMPRESS
referenced by:
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
referenced by:
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
referenced by:
DATE_CEIL
referenced by:
DATE_FLOOR
date_floor
::= DATE_FLOOR
referenced by:
DATE_ROUND
date_round
::= DATE_ROUND
referenced by:
DATE_TRUNC
date_trunc
::= DATE_TRUNC
referenced by:
Collect the day from a date.
Parameters:
•Input: A dateTime.
Returns: The day as an integer. DAY
referenced by:
Collect the day of a week from a date.
Parameters:
•Input: A dateTime.
Returns: The day of a week as an integer. DAYOFWEEK
referenced by:
Collect the day of a year from a date.
Parameters:
•Input: A dateTime.
Returns: The day of a year as an integer. DAYOFYEAR
referenced by:
DENSE_RANK
dense_rank
::= DENSE_RANK
referenced by:
DOUBLE_METAPHONE
double_metaphone
::= DOUBLE_METAPHONE
referenced by:
DOUBLE_METAPHONE_ALT
double_metaphone_alt
::= DOUBLE_METAPHONE_ALT
referenced by:
Divide one number by the second number.
Parameters:
•first: a number to divide.
•second: a number to divide with.
Returns: the divided output. DIVIDE
referenced by:
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
no references
EXP
referenced by:
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
referenced by:
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:
Collect the hour from a date.
Parameters:
•Input: A dateTime.
Returns: The hour as an integer. HOUR
referenced by:
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
referenced by:
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
referenced by:
JSONDECODE
jsondecode
::= JSONDECODE
referenced by:
JSONENCODE
jsonencode
::= JSONENCODE
referenced by:
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
referenced by:
levenshtein:
Determine the Levenshtein distance between two values as defined on Wikipedia.
referenced by:
COMMA
referenced by:
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
referenced by:
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
referenced by:
Converts provided string to lowercase.
Parameters:
•Input: the string that will be converted to lowercase.
Returns: A string converted to lowercase. LOWER
referenced by:
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
referenced by:
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
referenced by:
md5:
Converts a value to a 128-bit hash value as defined on Wikipedia.
•Input: Text to convert with MD5.
Returns: The input converted with MD5. MD5
referenced by:
metaphone:
Converts a value to the Metaphone code as defined on Wikipedia.
•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
referenced by:
METAPHONE3
metaphone3
::= METAPHONE3
referenced by:
METAPHONE3_ALT
metaphone3_alt
::= METAPHONE3_ALT
referenced by:
Get the remainder of a divide calculation.
Parameters:
•dividend: a number.
•divider: a number.
Returns: The remainder. MOD
referenced by:
Subtracts a value from another.
Parameters:
•Value: a number or datetime.
•Subtract: a number or datetime.
Returns: The value minus the subtraction. MINUS
referenced by:
Collect the minute from a date.
Parameters:
•Input: A dateTime.
Returns: The minute as an integer. MINUTE
referenced by:
Collect the month from a date.
Parameters:
•Input: A dateTime.
Returns: The month as an integer. MONTH
referenced by:
Creates a new Guid id.
Returns: The new Guid id.
NEWID
referenced by:
Coalesce all values together.
Returns: All values coalesced together.
NVL
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
RAND
referenced by:
RANK
referenced by:
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:
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
referenced by:
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:
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
referenced by:
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
referenced by:
Flips the input around.
Parameters:
•Input: text to flip around.
Returns: The text with it's characters in reversed order. REVERSE
referenced by:
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
referenced by:
ROW_NUMBER
row_number
::= ROW_NUMBER
referenced by:
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
referenced by:
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
referenced by:
Collect the microsecond from a date.
Parameters:
•Input: A dateTime.
Returns: The microsecond as an integer. MICROSECOND
referenced by:
Collect the millisecond from a date.
Parameters:
•Input: A dateTime.
Returns: The millisecond as an integer. MILLISECOND
referenced by:
NUMBER_TO_SPEECH
number_to_speech
::= NUMBER_TO_SPEECH
referenced by:
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
referenced by:
Collect the second from a date.
Parameters:
•Input: A dateTime.
Returns: The second as an integer. SECOND
referenced by:
soundex:
Converts a value to the Soundex code as defined on Wikipedia.
•Input: Text to that retrieve the soundex value from.
Returns: A text started with a number and followed by 3 digits. SOUNDEX
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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
referenced by:
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:
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
referenced by:
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
referenced by:
TO_HEX
referenced by:
Converts a text with unicodes to regular characters.
Parameters:
•Input: text with unicodes.
Returns: The input converted to all regular characters. UNISTR
referenced by:
Converts provided string to uppercase.
Parameters:
•Input: the string that will be converted to uppercase.
Returns: A string converted to uppercase. UPPER
referenced by:
Decodes a url.
Parameters:
•Url: url to decode.
Returns: The decoded url. URLDECODE
referenced by:
Encodes a url.
Parameters:
•Url: url to encode.
Returns: The encoded url. URLENCODE
referenced by:
Get the UNIX epoch time of a date/time.
Parameters:
•Input: A dateTime. Current date/time is used when no value is specified.
Returns: The UNIX epoch time. UNIX_TIMESTAMP
unix_timestamp
::= UNIX_TIMESTAMP
referenced by:
UNZIP
referenced by:
ZIP
referenced by:
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:
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
referenced by:
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
referenced by:
XMLELEMENT
xmlelement
::= XMLELEMENT
referenced by:
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
referenced by:
Pretty-print xml text.
Parameters:
•Xml: xml to pretty-print.
Returns: The pretty-printed XML text. XMLFORMAT
referenced by:
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
referenced by:
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
referenced by:
HTTPPOST
referenced by:
Collect the quarter from a date.
Parameters:
•Input: A dateTime.
Returns: The quarter as an integer. QUARTER
referenced by:
QUOTE_IDENT
referenced by:
QUOTE_LITERAL
quote_literal
::= QUOTE_LITERAL
referenced by:
QUOTE_NULLABLE
quote_nullable
::= QUOTE_NULLABLE
referenced by:
Gets the user log on code.
Returns: The user log on code.
USER
referenced by:
Collect the year from a date.
Parameters:
•Input: A dateTime.
Returns: The year as an integer. YEAR
referenced by:
TO_BINARY
referenced by:
Converts a value into text.
Parameters:
•Input: value to convert.
Returns: The input converted to text. TO_CHAR
referenced by:
Converts a value into a datetime.
Parameters:
•Input: value to convert.
Returns: The input converted to a datetime. TO_DATE
referenced by:
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
referenced by:
TO_NUMBER
referenced by:
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
referenced by:
The time of the system clock in local time at the device where Invantive UniversalSQL runs.
Returns: current date/time.
NOW GETDATE SYSDATETIME parenthesisOpen parenthesisClose SYSDATE
now ::= ( NOW | GETDATE | SYSDATETIME ) parenthesisOpen parenthesisClose
| SYSDATE
referenced by:
UTC_DATE parenthesisOpen parenthesisClose GETUTCDATE NOWUTC parenthesisOpen parenthesisClose SYSDATEUTC
utc ::= UTC_DATE ( parenthesisOpen parenthesisClose )?
| ( GETUTCDATE | NOWUTC ) parenthesisOpen parenthesisClose
| SYSDATEUTC
referenced by:
catalogIdentifier DOT schemaIdentifier DOT tableIdentifier
fullTableIdentifier
::= ( catalogIdentifier DOT ( schemaIdentifier? DOT )? )? tableIdentifier
referenced by:
identifier
catalogIdentifier
::= identifier
referenced by:
identifier
schemaIdentifier
::= identifier
referenced by:
identifier
tableIdentifier
::= identifier
referenced by:
alias DOT identifier
fieldIdentifier
::= ( alias DOT )? identifier
referenced by:
identifierWithMinus keywordsAsIdentifierOrAlias
attributeIdentifier
::= identifierWithMinus
| keywordsAsIdentifierOrAlias
referenced by:
identifier MINUS identifier INT_OR_DECIMAL_C ESCAPED_IDENTIFIER
identifierWithMinus
::= ESCAPED_IDENTIFIER
| identifier ( MINUS ( identifier | INT_OR_DECIMAL_C )? )*
referenced by:
ESCAPED_IDENTIFIER IDENTIFIER keywordsAsIdentifierOrAlias
identifier
::= ESCAPED_IDENTIFIER
| IDENTIFIER
| keywordsAsIdentifierOrAlias
referenced by:
ESCAPED_IDENTIFIER IDENTIFIER keywordsAsIdentifierOrAlias
alias ::= ESCAPED_IDENTIFIER
| IDENTIFIER
| keywordsAsIdentifierOrAlias
referenced by:
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:
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:
A constant text value with varchar2 data type.
STRING_C
referenced by:
•allColumnsSpecColumnNamePostfix
•allColumnsSpecColumnNamePrefix
•alterPersistentCacheDownloadStatement
•alterPersistentCacheDropStatement
•alterPersistentCacheSetStatement
•alterPersistentCacheSetTableOptions
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:
•ods
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
true false
booleanConstant
::= true
| false
referenced by:
•alterPersistentCacheSetStatement
•alterPersistentCacheSetTableOptions
•ods
The "unknown" value null.
NULL
referenced by:
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:
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:
pSqlItemDeclaration
pSqlDeclaration
::= pSqlItemDeclaration
referenced by:
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:
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:
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:
A PSQL block or statement defines a procedural step or a SQL statement to be executed.
pSqlBlock pSqlStatement
pSqlBlockOrStatement
::= pSqlBlock
| pSqlStatement
referenced by:
pSqlBlockOrStatement
pSqlBlockOrStatements
::= pSqlBlockOrStatement+
referenced by:
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:
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:
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:
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:
ELSIF booleanExpression THEN pSqlBlockOrStatements
pSqlElsIfExpression
::= ELSIF booleanExpression THEN pSqlBlockOrStatements
referenced by:
A variety of PSQL statements for loops are available.
pSqlForNumberLoopStatement pSqlForRecordLoopStatement pSqlWhileLoopStatement
pSqlLoopStatement
::= pSqlForNumberLoopStatement
| pSqlForRecordLoopStatement
| pSqlWhileLoopStatement
referenced by:
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:
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:
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:
IDENTIFIER
referenced by: