... |
|{anchor:token_TO_BYTES}_TO_BYTES_|[function|#function], [non-reserved identifier|#nonReserved]|| |{anchor:token_TO_CHARS}_TO_CHARS_|[function|#function], [non-reserved identifier|#nonReserved]|| |
|{anchor:token_TRIM}_TRIM_|[function|#function], [non-reserved identifier|#nonReserved], [text table column|#textColumn]|| column|#textColumn], [text table|#textTable]|| |
|{anchor:token_VARIADIC}_VARIADIC_|[non-reserved identifier|#nonReserved], [procedure parameter|#procedureParameter]|| |{anchor:token_VAR_POP}_VAR_POP_|[standard aggregate function|#aggregateSymbol], [non-reserved identifier|#nonReserved]|| |
... |
h3. {anchor:textTable}_[text table|#usage_textTable]_ ::= |
- |[TEXTTABLE|#token_TEXTTABLE] <[lparen|#token_LPAREN]> <[common value expression|#commonValueExpression]> ( [SELECTOR|#token_SELECTOR] <[string|#stringVal]> )? [COLUMNS|#token_COLUMNS] <[text table column|#textColumn]> ( <[comma|#token_COMMA]> <[text table column|#textColumn]> )\* ( ( [NO|#token_NO] [ROW|#token_ROW] [DELIMITER|#token_DELIMITER] ) \| ( [ROW|#token_ROW] [DELIMITER|#token_DELIMITER] <[character|#charVal]> ) )? ( [DELIMITER|#token_DELIMITER] <[character|#charVal]> )? ( ( [ESCAPE|#token_ESCAPE] <[character|#charVal]> ) \| ( [QUOTE|#token_QUOTE] <[character|#charVal]> ) )? ( [HEADER|#token_HEADER] ( <[unsigned integer|#intVal]> )? )? ( [SKIP|#token_SKIP_KEYWORD] <[unsigned integer|#intVal]> )? ( [NO|#token_NO] [TRIM|#token_TRIM] )? <[rparen|#token_RPAREN]> ( [AS|#token_AS] )? <[identifier|#id]>|| |
The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference. |
... |
Example: |
{code:sql}QUERYSTRING('path', col1 AS opt, col2 AS val){code} |
|
... |
ALLOCATE | ARE | ARRAY | ASENSITIVE | ASYMETRIC | AUTHORIZATION | BINARY | CALLED |
CASCADED | CHARACTER | CHECK | CLOSE | COLLATE | COMMIT | CONNECT | CORRESPONDING |
CRITERIA | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_USER | CURSOR | CYCLE | DATALINK |
DEALLOCATE | DEC | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT | DLNEWCOPY | DLPREVIOUSCOPY |
DLURLCOMPLETE | DLURLCOMPLETEONLY | DLURLCOMPLETEWRITE | DLURLPATH | DLURLPATHONLY | DLURLPATHWRITE | DLURLSCHEME | DLURLSERVER |
DLVALUE | DYNAMIC | ELEMENT | EXTERNAL | FREE | GET | GRANT | HAS |
HOLD | IDENTITY | IMPORT | INDICATOR | INPUT | INSENSITIVE | INT | INTERVAL |
ISOLATION | LARGE | LOCALTIME | LOCALTIMESTAMP | MATCH | MEMBER | METHOD | MODIFIES |
MODULE | MULTISET | NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NONE |
NUMERIC | OLD | OPEN | OUTPUT | OVERLAPS | PRECISION | PREPARE | RANGE |
READS | RECURSIVE | REFERENCING | RELEASE | REVOKE | ROLLBACK | SAVEPOINT | SCROLL |
SEARCH | SENSITIVE | SESSION_USER | SPECIFIC | SPECIFICTYPE | SQL | START | STATIC |
SUBMULTILIST | SYMETRIC | SYSTEM | SYSTEM_USER | TIMEZONE_HOUR | TIMEZONE_MINUTE | TRANSLATION | TREAT |
VALUE | VARYING | WHENEVER | WINDOW | WITHIN | XMLBINARY | XMLCAST | XMLDOCUMENT |
XMLEXISTS | XMLITERATE | XMLTEXT | XMLVALIDATE |
A string literal value. Use '' to escape ' in the string.
Example:
'a string'
'it''s a string'
Allows non-reserved keywords to be parsed as identifiers
Example:
SELECT COUNT FROM ...
Partial or full name of a single entity.
Example:
tbl.col
"tbl"."col"
Creates a trigger action on the given target.
Example:
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
ALTER ( ( VIEW <identifier> AS <query expression> ) | ( PROCEDURE <identifier> AS <statement> ) | ( TRIGGER ON <identifier> INSTEAD OF ( INSERT | UPDATE | DELETE ) ( ( AS <for each row trigger action> ) | ENABLED | DISABLED ) ) ) |
Alter the given target.
Example:
ALTER VIEW vw AS SELECT col FROM tbl
Defines an action to perform on each row.
Example:
FOR EACH ROW BEGIN ATOMIC ... END
<alter> |
A statement that can be executed at runtime.
Example:
SELECT * FROM tbl
Creates a trigger action on the given target.
Example:
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
CREATE ( LOCAL )? TEMPORARY TABLE <identifier> <lparen> <temporary table element> ( <comma> <temporary table element> )* ( <comma> PRIMARY KEY <column list> )? <rparen> |
Creates a temporary table.
Example:
CREATE LOCAL TEMPORARY TABLE tmp (col integer)
<identifier> ( <data type> | SERIAL ) ( NOT NULL )? |
Defines a temporary table column.
Example:
col string NOT NULL
Raises an error with the given message.
Example:
ERROR 'something went wrong'
RAISE ( SQLWARNING )? <exception reference> |
Raises an error or warning with the given message.
Example:
RAISE SQLEXCEPTION 'something went wrong'
a reference to an exception
Example:
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
SQLEXCEPTION <common value expression> ( SQLSTATE <common value expression> ( <comma> <common value expression> )? )? ( CHAIN <exception reference> )? |
creates a sql exception or warning with the specified message, state, and code
Example:
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
( ( <identifier> <colon> )? ( <loop statement> | <while statement> | <compound statement> ) ) |
A procedure statement.
Example:
IF (x = 5) BEGIN ... END
( <assignment statement> | <data statement> | <raise error statement> | <raise statement> | <declare statement> | <branching statement> | <return statement> ) <semicolon> |
A procedure statement terminated by ;.
Example:
SELECT * FROM tbl;
A procedure statement block contained in BEGIN END.
Example:
BEGIN NOT ATOMIC ... END
( ( BREAK | CONTINUE ) ( <identifier> )? ) |
( LEAVE <identifier> ) |
A procedure branching control statement, which typically specifies a label to return control to.
Example:
BREAK x
RETURN ( <expression> )? |
A return statement.
Example:
RETURN 1
A procedure while statement that executes until its condition is false.
Example:
WHILE (var) BEGIN ... END
LOOP ON <lparen> <query expression> <rparen> AS <identifier> <statement> |
A procedure loop statement that executes over the given cursor.
Example:
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END
A procedure loop statement that executes over the given cursor.
Example:
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END
DECLARE ( <data type> | EXCEPTION ) <identifier> ( <eq> <assignment statement operand> )? |
A procedure declaration statement that creates a variable and optionally assigns a value.
Example:
DECLARE STRING x = 'a'
<identifier> <eq> ( <assignment statement operand> | ( <call statement> ( ( WITH | WITHOUT ) RETURN )? ) ) |
Assigns a variable a value in a procedure.
Example:
x = 'b'
A value or command that can be used in an assignment.
All assigments except for expression are deprecated. |
( <directly executable statement> | <dynamic data statement> ) ( ( WITH | WITHOUT ) RETURN )? |
A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.
Defines a procedure body on a Procedure metadata object.
Example:
BEGIN ... END
( EXECUTE | EXEC ) ( STRING | IMMEDIATE )? <expression> ( AS <typed element list> ( INTO <identifier> )? )? ( USING <set clause list> )? ( UPDATE ( <unsigned integer> | <star> ) )? |
A procedure statement that can execute arbitrary sql.
Example:
EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp
<identifier> <eq> <expression> ( <comma> <identifier> <eq> <expression> )* |
A list of value assignments.
Example:
col1 = 'x', col2 = 'y' ...
<identifier> <data type> ( <comma> <identifier> <data type> )* |
A list of typed elements.
Example:
col1 string, col2 integer ...
<lbrace> ( <qmark> <eq> )? CALL <identifier> ( <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> )? <rbrace> ( <option clause> )? |
A callable statement defined using JDBC escape syntax.
Example:
{? = CALL proc}
( ( EXEC | EXECUTE | CALL ) <identifier> <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> ) ( <option clause> )? |
Executes the procedure with the given parameters.
Example:
CALL proc('a', 1)
( <identifier> <eq> ( <gt> )? <expression> ( <comma> <identifier> <eq> ( <gt> )? <expression> )* ) |
A list of named parameters.
Example:
param1 => 'x', param2 => 1
( INSERT | MERGE ) INTO <identifier> ( <column list> )? <query expression> ( <option clause> )? |
Inserts values into the given target.
Example:
INSERT INTO tbl (col1, col2) VALUES ('a', 1)
<expression> ( <comma> <expression> )* |
A list of expressions.
Example:
col1, 'a', ...
UPDATE <identifier> SET <set clause list> ( <where clause> )? ( <option clause> )? |
Update values in the given target.
Example:
UPDATE tbl SET (col1 = 'a') WHERE col2 = 1
DELETE FROM <identifier> ( <where clause> )? ( <option clause> )? |
Delete rows from the given target.
Example:
DELETE FROM tbl WHERE col2 = 1
( WITH <with list element> ( <comma> <with list element> )* )? <query expression body> |
A declarative query for data.
Example:
SELECT * FROM tbl WHERE col2 = 1
<identifier> ( <column list> )? AS <lparen> <query expression> <rparen> |
A query expression for use in the enclosing query.
Example:
X (Y, Z) AS (SELECT 1, 2)
<query term> ( ( UNION | EXCEPT ) ( ALL | DISTINCT )? <query term> )* ( <order by clause> )? ( <limit clause> )? ( <option clause> )? |
The body of a query expression, which can optionally be ordered and limited.
Example:
SELECT * FROM tbl ORDER BY col1 LIMIT 1
<query primary> ( INTERSECT ( ALL | DISTINCT )? <query primary> )* |
Used to establish INTERSECT precedence.
Example:
SELECT * FROM tbl
SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2
<query> |
( VALUES <lparen> <expression list> <rparen> ( <comma> <lparen> <expression list> <rparen> )* ) |
( TABLE <identifier> ) |
( <lparen> <query expression body> <rparen> ) |
A declarative source of rows.
Example:
TABLE tbl
SELECT * FROM tbl1
<select clause> ( <into clause> )? ( <from clause> ( <where clause> )? ( <group by clause> )? ( <having clause> )? )? |
A SELECT query.
Example:
SELECT col1, max(col2) FROM tbl GROUP BY col1
Used to direct the query into a table.
This is deprecated. Use INSERT INTO with a query expression instead. |
Example:
INTO tbl
SELECT ( ALL | DISTINCT )? ( <star> | ( <select sublist> ( <comma> <select sublist> )* ) ) |
The columns returned by a query. Can optionally be distinct.
Example:
SELECT *
SELECT DISTINCT a, b, c
<all in group > |
An element in the select clause
Example:
tbl.*
tbl.col AS x
( <expression> ( ( AS )? <identifier> )? ) |
A select clause item that selects a single column.
This is slightly different than a derived column in that the AS keyword is optional. |
Example:
tbl.col AS x
( <expression> ( AS <identifier> )? ) |
An optionally named expression.
Example:
tbl.col AS x
A select sublist that can select all columns from the given group.
Example:
tbl.*
( XMLAGG | ARRAY_AGG | JSONARRAY_AGG ) <lparen> <expression> ( <order by clause> )? <rparen> |
An aggregate function that can optionally be ordered.
Example:
XMLAGG(col1) ORDER BY col2
ARRAY_AGG(col1)
TEXTAGG <lparen> ( FOR )? <derived column> ( <comma> <derived column> )* ( DELIMITER <character> )? ( QUOTE <character> )? ( HEADER )? ( ENCODING <identifier> )? ( <order by clause> )? <rparen> |
An aggregate function for creating separated value clobs.
Example:
TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)
A standard aggregate function.
Example:
COUNT(*)
( ROW_NUMBER | RANK | DENSE_RANK ) <lparen> <rparen> |
An analytic aggregate function.
Example:
ROW_NUMBER()
FILTER <lparen> WHERE <boolean primary> <rparen> |
An aggregate filter clause applied prior to accumulating the value.
Example:
FILTER (WHERE col1='a')
FROM ( <table reference> ( <comma> <table reference> )* ) |
A query from clause containing a list of table references.
Example:
FROM a, b
FROM a right outer join b, c, d join e".</p>
( <escaped join> <joined table> <rbrace> ) |
An optionally escaped joined table.
Example:
a
a inner join b
<table primary> ( <cross join> | <qualified table> )* |
A table or join.
Example:
a
a inner join b
( ( CROSS | UNION ) JOIN <table primary> ) |
A cross join.
Example:
a CROSS JOIN b
An INNER or OUTER join.
Example:
a inner join b
( <text table> | <array table> | <xml table> | <object table> | <table name> | <table subquery> | ( <lparen> <joined table> <rparen> ) ) ( ( MAKEDEP <make dep options> ) | MAKENOTDEP )? |
A single source of rows.
Example:
a
options for the make dep hint
Example:
(min:10000)
XMLSERIALIZE <lparen> ( DOCUMENT | CONTENT )? <expression> ( AS ( STRING | VARCHAR | CLOB | VARBINARY | BLOB ) )? ( ENCODING <identifier> )? ( VERSION <string> )? ( ( INCLUDING | EXCLUDING ) XMLDECLARATION )? <rparen> |
Serializes an XML value.
Example:
XMLSERIALIZE(col1 AS CLOB)
ARRAYTABLE <lparen> <value expression primary> COLUMNS <typed element list> <rparen> ( AS )? <identifier> |
The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.
Example:
ARRAYTABLE (col1 COLUMNS x STRING) AS y
TEXTTABLE <lparen> <common value expression> ( SELECTOR <string> )? COLUMNS <text table column> ( <comma> <text table column> )* ( ( NO ROW DELIMITER ) | ( ROW DELIMITER <character> ) )? ( DELIMITER <character> )? ( ( ESCAPE <character> ) | ( QUOTE <character> ) )? ( HEADER ( <unsigned integer> )? )? ( SKIP <unsigned integer> )? ( NO TRIM )? <rparen> ( AS )? <identifier> |
The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.
Example:
TEXTTABLE (file COLUMNS x STRING) AS y
<identifier> ( ( FOR ORDINALITY ) | ( <data type> ( WIDTH <unsigned integer> ( NO TRIM )? )? ( SELECTOR <string> <unsigned integer> )? ) ) |
A text table column.
Example:
x INTEGER WIDTH 6
XMLQUERY <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( ( NULL | EMPTY ) ON EMPTY )? <rparen> |
Executes an XQuery to return an XML result.
Example:
XMLQUERY('<a>...</a>' PASSING doc)
OBJECTTABLE <lparen> ( LANGUAGE <string> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? COLUMNS <object table column> ( <comma> <object table column> )* <rparen> ( AS )? <identifier> |
Returns table results by processing a script.
Example:
OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X
<identifier> <data type> <string> ( DEFAULT <expression> )? |
object table column.
Example:
y integer 'teiid_row_number'
XMLTABLE <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( COLUMNS <xml table column> ( <comma> <xml table column> )* )? <rparen> ( AS )? <identifier> |
Returns table results by processing an XQuery.
Example:
XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X
<identifier> ( ( FOR ORDINALITY ) | ( <data type> ( DEFAULT <expression> )? ( PATH <string> )? ) ) |
XML table column.
Example:
y FOR ORDINALITY
An unsigned interger value.
Example:
12345
( TABLE | LATERAL )? <lparen> ( <query expression> | <call statement> ) <rparen> ( AS )? <identifier> |
A table defined by a subquery.
Example:
(SELECT * FROM tbl) AS x
( <identifier> ( ( AS )? <identifier> )? ) |
A table named in the FROM clause.
Example:
tbl AS x
Specifies a search condition
Example:
WHERE x = 'a'
A boolean expression.
<boolean term> ( OR <boolean term> )* |
An optionally ORed boolean expression.
<boolean factor> ( AND <boolean factor> )* |
An optional ANDed boolean factor.
( NOT )? <boolean primary> |
A boolean factor.
Example:
NOT x = 'a'
( <common value expression> ( <between predicate> | <match predicate> | <like regex predicate> | <in predicate> | <is null predicate> | <quantified comparison predicate> | <comparison predicate> )? ) |
A boolean predicate or simple expression.
Example:
col LIKE 'a%'
A comparison operator.
Example:
=
A value comparison.
Example:
= 'a'
<lparen> ( <query expression> | <call statement> ) <rparen> |
A subquery.
Example:
(SELECT * FROM tbl)
<comparison operator> ( ANY | SOME | ALL ) <subquery> |
A subquery comparison.
Example:
= ANY (SELECT col FROM tbl)
Matches based upon a pattern.
Example:
LIKE 'a_'
( NOT )? LIKE_REGEX <common value expression> |
A regular expression match.
Example:
LIKE_REGEX 'a.*b'
<string> |
A single character.
Example:
'a'
A comparison between two values.
Example:
BETWEEN 1 AND 5
A null test.
Example:
IS NOT NULL
( NOT )? IN ( <subquery> | ( <lparen> <common value expression> ( <comma> <common value expression> )* <rparen> ) ) |
A comparison with multiple values.
Example:
IN (1, 5)
A test if rows exist.
Example:
EXISTS (SELECT col FROM tbl)
GROUP BY ( ROLLUP <lparen> <expression list> <rparen> | <expression list> ) |
Defines the grouping columns
Example:
GROUP BY col1, col2
Search condition applied after grouping.
Example:
HAVING max(col1) = 5
ORDER BY <sort specification> ( <comma> <sort specification> )* |
Specifices row ordering.
Example:
ORDER BY x, y DESC
Defines how to sort on a particular expression
Example:
col1 NULLS FIRST
A sort expression.
Example:
col1
A literal integer or parameter reference to an integer.
Example:
?
( LIMIT <integer parameter> ( <comma> <integer parameter> )? ) |
( OFFSET <integer parameter> ( ROW | ROWS ) ( <fetch clause> )? ) |
Limits and/or offsets the resultant rows.
Example:
LIMIT 2
ANSI limit.
Example:
FETCH FIRST 1 ROWS ONLY
OPTION ( MAKEDEP <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKENOTDEP <identifier> ( <comma> <identifier> )* | NOCACHE ( <identifier> ( <comma> <identifier> )* )? )* |
Specifies query options.
Example:
OPTION MAKEDEP tbl
A value.
Example:
col1
( <numeric value expression> ( <concat_op> <numeric value expression> )* ) |
Establishes the precedence of concat.
Example:
'a' || 'b'
( <term> ( <plus or minus> <term> )* ) |
Example:
1 + 2
The + or - operator.
Example:
+
( <value expression primary> ( <star or slash> <value expression primary> )* ) |
A numeric term
Example:
1 * 2
The * or / operator.
Example:
/
( <plus or minus> )? ( <unsigned numeric literal> | ( <unsigned value expression primary> ( <lsbrace> <numeric value expression> <rsbrace> )* ) ) |
A simple value expression.
Example:
+col1
<qmark> |
( <dollar> <unsigned integer> ) |
A parameter reference to be bound later.
Example:
?
( ( <text aggreate function> | <standard aggregate function> | <ordered aggreate function> ) ( <filter clause> )? ( <window specification> )? ) |
( <analytic aggregate function> ( <filter clause> )? <window specification> ) |
( <function> ( <window specification> )? ) |
( <lparen> ( <expression> ( <comma> <expression> )* )? ( <comma> )? <rparen> ) |
An expression nested in parens
Example:
(1)
( <escaped function> <function> <rbrace> ) |
<subquery> |
An unsigned simple value expression.
Example:
col1
OVER <lparen> ( PARTITION BY <expression list> )? ( <order by clause> )? <rparen> |
The window specification for an analytical or windowed aggregate function.
Example:
OVER (PARTION BY col1)
CASE <expression> ( WHEN <expression> THEN <expression> )+ ( ELSE <expression> )? END |
If/then/else chain using a common search predicand.
Example:
CASE col1 WHEN 'a' THEN 1 ELSE 2
CASE ( WHEN <condition> THEN <expression> )+ ( ELSE <expression> )? END |
If/then/else chain using multiple search conditions.
Example:
CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2
( SUBSTRING <lparen> <expression> ( ( FROM <expression> ( FOR <expression> )? ) | ( <comma> <expression list> ) ) <rparen> ) |
( TRIM <lparen> ( ( ( ( LEADING | TRAILING | BOTH ) ( <expression> )? ) | <expression> ) FROM )? <expression> <rparen> ) |
( ( TO_CHARS | TO_BYTES ) <lparen> <expression> <comma> <string> ( <comma> <expression> )? <rparen> ) |
( ( TIMESTAMPADD | TIMESTAMPDIFF ) <lparen> <time interval> <comma> <expression> <comma> <expression> <rparen> ) |
( ( TRANSLATE | INSERT ) <lparen> ( <expression list> )? <rparen> ) |
( XMLPI <lparen> ( ( NAME )? <identifier> ) ( <comma> <expression> )? <rparen> ) |
( <identifier> <lparen> ( ALL | DISTINCT )? ( <expression list> )? ( <order by clause> )? <rparen> ( <filter clause> )? ) |
Calls a scalar function.
Example:
func('1', col1)
XMLPARSE <lparen> ( DOCUMENT | CONTENT ) <expression> ( WELLFORMED )? <rparen> |
Parses the given value as XML.
Example:
XMLPARSE(DOCUMENT doc WELLFORMED)
QUERYSTRING <lparen> <expression> ( <comma> <derived column> )* <rparen> |
Produces a URL query string from the given arguments.
Example:
QUERYSTRING('path', col1 AS opt, col2 AS val)
XMLELEMENT <lparen> ( ( NAME )? <identifier> ) ( <comma> <xml namespaces> )? ( <comma> <xml attributes> )? ( <comma> <expression> )* <rparen> |
Creates an XML element.
Example:
XMLELEMENT(NAME "root", child)
XMLATTRIBUTES <lparen> <derived column> ( <comma> <derived column> )* <rparen> |
Creates attributes for the containing element.
Example:
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)
Produces a JSON object containing name value pairs.
Example:
JSONOBJECT(col1 AS val1, col2 AS val2)
<derived column> ( <comma> <derived column> )* |
a list of name value pairs
Example:
col1 AS val1, col2 AS val2
XMLFOREST <lparen> ( <xml namespaces> <comma> )? <derived column list> <rparen> |
Produces an element for each derived column.
Example:
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)
XMLNAMESPACES <lparen> <xml namespace element> ( <comma> <xml namespace element> )* <rparen> |
Defines XML namespace URI/prefix combinations
Example:
XMLNAMESPACES('http://foo' AS foo)
( <string> AS <identifier> ) |
An xml namespace
Example:
NO DEFAULT
( STRING ( <lparen> <unsigned integer> <rparen> )? ) |
( VARCHAR ( <lparen> <unsigned integer> <rparen> )? ) |
( CHAR ( <lparen> <unsigned integer> <rparen> )? ) |
( BIGINTEGER ( <lparen> <unsigned integer> <rparen> )? ) |
( BIGDECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? ) |
( DECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? ) |
( OBJECT ( <lparen> <unsigned integer> <rparen> )? ) |
( BLOB ( <lparen> <unsigned integer> <rparen> )? ) |
( CLOB ( <lparen> <unsigned integer> <rparen> )? ) |
( VARBINARY ( <lparen> <unsigned integer> <rparen> )? ) |
A non-collection data type.
Example:
STRING
<simple data type> ( <lsbrace> <rsbrace> )* |
A data type.
Example:
STRING[]
A time interval keyword.
Example:
SQL_TSI_HOUR
<string> |
( <escaped type> <string> <rbrace> ) |
An escaped or simple non numeric literal.
Example:
'a'
An unsigned numeric literal value.
Example:
1.234
( <create table> | <create procedure> | <option namespace> | <alter options> | <create trigger> ) ( <semicolon> )? |
A data definition statement.
Example:
CREATE FOREIGN TABLE X (Y STRING)
SET NAMESPACE <string> AS <identifier> |
A namespace used to shorten the full name of an option key.
Example:
SET NAMESPACE 'http://foo' AS foo
CREATE ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) ( <identifier> <lparen> ( <procedure parameter> ( <comma> <procedure parameter> )* )? <rparen> ( RETURNS ( ( ( TABLE )? <lparen> <procedure result column> ( <comma> <procedure result column> )* <rparen> ) | <data type> ) )? ( <options clause> )? ( AS <statement> )? ) |
Defines a procedure or function invocation.
Example:
CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING
A procedure or function parameter
Example:
OUT x INTEGER
<identifier> <data type> ( NOT NULL )? ( <options clause> )? |
A procedure result column.
Example:
x INTEGER
CREATE ( ( FOREIGN TABLE ) | ( ( VIRTUAL )? VIEW ) | ( GLOBAL TEMPORARY TABLE ) ) <identifier> ( <create table body> | ( <options clause> )? ) ( AS <query expression> )? |
Defines a table or view.
Example:
CREATE VIEW vw AS SELECT 1
CREATE ( LOCAL )? FOREIGN TEMPORARY TABLE <identifier> <create table body> ON <identifier> |
Defines a foreign temp table
Example:
CREATE FOREIGN TEMPORARY TABLE t (x string) ON z
<lparen> <table element> ( <comma> <table element> )* ( <comma> ( CONSTRAINT <identifier> )? ( <primary key> | <other constraints> | <foreign key> ) ( <options clause> )? )* <rparen> ( <options clause> )? |
Defines a table.
Example:
(x string) OPTIONS (CARDINALITY 100)
FOREIGN KEY <column list> REFERENCES <identifier> ( <column list> )? |
Defines the foreign key referential constraint.
Example:
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
Defines the primary key.
Example:
PRIMARY KEY (a, b)
( ( UNIQUE | ACCESSPATTERN ) <column list> ) |
( INDEX <lparen> <expression list> <rparen> ) |
Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.
Example:
UNIQUE (a)
<lparen> <identifier> ( <comma> <identifier> )* <rparen> |
A list of column names.
Example:
(a, b)
<identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) ( ( PRIMARY KEY ) | ( ( UNIQUE )? ( INDEX )? ) ) ( DEFAULT <string> )? ( <options clause> )? |
Defines a table column.
Example:
x INTEGER NOT NULL
OPTIONS <lparen> <option pair> ( <comma> <option pair> )* <rparen> |
A list of statement options.
Example:
OPTIONS ('x' 'y', 'a' 'b')
<identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> ) |
An option key/value pair.
Example:
'key' 'value'
ALTER ( VIRTUAL | FOREIGN )? ( TABLE | VIEW | PROCEDURE ) <identifier> ( <alter options list> | <alter column options> ) |
alters options of tables/procedure
Example:
ALTER FOREIGN TABLE foo OPTIONS (ADD cardinality 100)
OPTIONS <lparen> ( <add set option> | <drop option> ) ( <comma> ( <add set option> | <drop option> ) )* <rparen> |
a list of alterations to options
Example:
OPTIONS (ADD updatable true)
drop option
Example:
DROP updatable
( ADD | SET ) <option pair> |
add or set an option pair
Example:
ADD updatable true
ALTER ( COLUMN | PARAMETER )? <identifier> <alter options list> |
alters a set of column options
Example:
ALTER COLUMN bar OPTIONS (ADD updatable true)