• Main Entry Points
  • callable statement
  • ddl statement
  • procedure body definition
  • directly executable statement
• Reserved Keywords
• Non-Reserved Keywords
• Reserved Keywords For Future Use
• Tokens
• Production Cross-Reference
• Productions

Reserved Keywords

Non-Reserved Keywords

Reserved Keywords For Future Use

Tokens

Production Cross-Reference

Productions

string ::=

• <string literal>

A string literal value. Use '' to escape ' in the string.

Example:

'a string'

'it''s a string'

non-reserved identifier ::=

• INSTEAD

• VIEW

• ENABLED

• DISABLED

• KEY

• SERIAL

• TEXTAGG

• COUNT

• ROW_NUMBER

• RANK

• DENSE_RANK

• SUM

• AVG

• MIN

• MAX

• EVERY

• STDDEV_POP

• STDDEV_SAMP

• VAR_SAMP

• VAR_POP

• DOCUMENT

• CONTENT

• TRIM

• EMPTY

• ORDINALITY

• PATH

• FIRST

• LAST

• NEXT

• SUBSTRING

• EXTRACT

• TO_CHARS

• TO_BYTES

• TIMESTAMPADD

• TIMESTAMPDIFF

• QUERYSTRING

• NAMESPACE

• RESULT

• INDEX

• ACCESSPATTERN

• AUTO_INCREMENT

• WELLFORMED

• SQL_TSI_FRAC_SECOND

• SQL_TSI_SECOND

• SQL_TSI_MINUTE

• SQL_TSI_HOUR

• SQL_TSI_DAY

• SQL_TSI_WEEK

• SQL_TSI_MONTH

• SQL_TSI_QUARTER

• SQL_TSI_YEAR

• TEXTTABLE

• ARRAYTABLE

• SELECTOR

• SKIP

• WIDTH

• PASSING

• NAME

• ENCODING

• COLUMNS

• DELIMITER

• QUOTE

• HEADER

• NULLS

• OBJECTTABLE

• VERSION

• INCLUDING

• EXCLUDING

• XMLDECLARATION

• VARIADIC

• RAISE

• EXCEPTION

• CHAIN

• JSONARRAY_AGG

• JSONOBJECT

Allows non-reserved keywords to be parsed as identifiers

Example:
SELECT COUNT FROM ...

identifier ::=

• <identifier>

• <non-reserved identifier>

Partial or full name of a single entity.

Example:

tbl.col

"tbl"."col"

create trigger ::=

• CREATE TRIGGER ON <identifier> INSTEAD OF ( INSERT | UPDATE | DELETE ) AS <for each row trigger action>

Creates a trigger action on the given target.

Example:

CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END

alter ::=

• 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

for each row trigger action ::=

• FOR EACH ROW ( ( BEGIN ( ATOMIC )? ( <statement> )* END ) | <statement> )

Defines an action to perform on each row.

Example:

FOR EACH ROW BEGIN ATOMIC ... END

directly executable statement ::=

• <query expression>

• <call statement>

• <insert statement>

• <update statement>

• <delete statement>

• <drop table>

• <create temporary table>

• <create foreign temp table>

• <alter>

• <create trigger>

• <compound statement>

A statement that can be executed at runtime.

Example:

SELECT * FROM tbl

drop table ::=

• DROP TABLE <identifier>

Creates a trigger action on the given target.

Example:

CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END

create temporary table ::=

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

temporary table element ::=

• <identifier> ( <data type> | SERIAL ) ( NOT NULL )?

Defines a temporary table column.

Example:

col string NOT NULL

raise error statement ::=

• ERROR <expression>

Raises an error with the given message.

Example:

ERROR 'something went wrong'

raise statement ::=

• RAISE ( SQLWARNING )? <exception reference>

Raises an error or warning with the given message.

Example:

RAISE SQLEXCEPTION 'something went wrong'

exception reference ::=

• <identifier>

• <sql exception>

a reference to an exception

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

sql exception ::=

• 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

statement ::=

• ( ( <identifier> <colon> )? ( <loop statement> | <while statement> | <compound statement> ) )

• <if statement> | <delimited statement>

A procedure statement.

Example:

IF (x = 5) BEGIN ... END

delimited statement ::=

• ( <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;

compound statement ::=

• BEGIN ( ( NOT )? ATOMIC )? ( <statement> )* ( EXCEPTION <identifier> ( <statement> )* )? END

A procedure statement block contained in BEGIN END.

Example:

BEGIN NOT ATOMIC ... END

branching statement ::=

• ( ( BREAK | CONTINUE ) ( <identifier> )? )

• ( LEAVE <identifier> )

A procedure branching control statement, which typically specifies a label to return control to.

Example:

BREAK x

return statement ::=

• RETURN ( <expression> )?

A return statement.

Example:

RETURN 1

while statement ::=

• WHILE <lparen> <condition> <rparen> <statement>

A procedure while statement that executes until its condition is false.

Example:

WHILE (var) BEGIN ... END

loop statement ::=

• 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

if statement ::=

• IF <lparen> <condition> <rparen> <statement> ( ELSE <statement> )?

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

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

assignment statement ::=

• <identifier> <eq> ( <assignment statement operand> | ( <call statement> ( ( WITH | WITHOUT ) RETURN )? ) )

Assigns a variable a value in a procedure.

Example:

x = 'b'

assignment statement operand ::=

• <insert statement>

• <update statement>

• <delete statement>

• <expression>

• <query expression>

• <sql exception>

A value or command that can be used in an assignment.

All assigments except for expression are deprecated.

data statement ::=

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

procedure body definition ::=

• ( CREATE ( VIRTUAL )? PROCEDURE )? <statement>

Defines a procedure body on a Procedure metadata object.

Example:

BEGIN ... END

dynamic data statement ::=

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

set clause list ::=

• <identifier> <eq> <expression> ( <comma> <identifier> <eq> <expression> )*

A list of value assignments.

Example:

col1 = 'x', col2 = 'y' ...

typed element list ::=

• <identifier> <data type> ( <comma> <identifier> <data type> )*

A list of typed elements.

Example:

col1 string, col2 integer ...

callable statement ::=

• <lbrace> ( <qmark> <eq> )? CALL <identifier> ( <lparen> ( <expression list> )? <rparen> )? <rbrace> ( <option clause> )?

A callable statement defined using JDBC escape syntax.

Example:

{? = CALL proc}

call statement ::=

• ( ( 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)

named parameter list ::=

• ( <identifier> <eq> ( <gt> )? <expression> ( <comma> <identifier> <eq> ( <gt> )? <expression> )* )

A list of named parameters.

Example:

param1 => 'x', param2 => 1

insert statement ::=

• ( INSERT | MERGE ) INTO <identifier> ( <column list> )? ( ( VALUES <lparen> <expression list> <rparen> ) | <query expression> ) ( <option clause> )?

Inserts values into the given target.

Example:

INSERT INTO tbl (col1, col2) VALUES ('a', 1)

expression list ::=

• <expression> ( <comma> <expression> )*

A list of expressions.

Example:

col1, 'a', ...

update statement ::=

• 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 statement ::=

• DELETE FROM <identifier> ( <where clause> )? ( <option clause> )?

Delete rows from the given target.

Example:

DELETE FROM tbl WHERE col2 = 1

query expression ::=

• ( WITH <with list element> ( <comma> <with list element> )* )? <query expression body>

A declarative query for data.

Example:

SELECT * FROM tbl WHERE col2 = 1

with list element ::=

• <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 expression body ::=

• <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 term ::=

• <query primary> ( INTERSECT ( ALL | DISTINCT )? <query primary> )*

Used to establish INTERSECT precedence.

Example:

SELECT * FROM tbl

SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2

query primary ::=

• <query>

• ( TABLE <identifier> )

• ( <lparen> <query expression body> <rparen> )

A declarative source of rows.

Example:

TABLE tbl

SELECT * FROM tbl1

query ::=

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

into clause ::=

• INTO <identifier>

Used to direct the query into a table.

This is deprecated. Use INSERT INTO with a query expression instead.

Example:

INTO tbl

select clause ::=

• 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

select sublist ::=

• <select derived column>

• <all in group >

An element in the select clause

Example:

tbl.*

tbl.col AS x

select derived column ::=

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

derived column ::=

• ( <expression> ( AS <identifier> )? )

An optionally named expression.

Example:

tbl.col AS x

all in group ::=

• <all in group identifier>

A select sublist that can select all columns from the given group.

Example:

tbl.*

ordered aggreate function ::=

• ( 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)

text aggreate function ::=

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

standard aggregate function ::=

• ( COUNT <lparen> <star> <rparen> )

• ( ( COUNT | SUM | AVG | MIN | MAX | EVERY | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | SOME | ANY ) <lparen> ( DISTINCT | ALL )? <expression> <rparen> )

A standard aggregate function.

Example:

COUNT(*)

analytic aggregate function ::=

• ( ROW_NUMBER | RANK | DENSE_RANK ) <lparen> <rparen>

An analytic aggregate function.

Example:

ROW_NUMBER()

filter clause ::=

• FILTER <lparen> WHERE <boolean primary> <rparen>

An aggregate filter clause applied prior to accumulating the value.

Example:

FILTER (WHERE col1='a')

from clause ::=

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

table reference ::=

• ( <escaped join> <joined table> <rbrace> )

• <joined table>

An optionally escaped joined table.

Example:

a

a inner join b

joined table ::=

• <table primary> ( <cross join> | <qualified table> )*

A table or join.

Example:

a

a inner join b

cross join ::=

• ( ( CROSS | UNION ) JOIN <table primary> )

A cross join.

Example:

a CROSS JOIN b

qualified table ::=

• ( ( ( RIGHT ( OUTER )? ) | ( LEFT ( OUTER )? ) | ( FULL ( OUTER )? ) | INNER )? JOIN <table reference> ON <condition> )

An INNER or OUTER join.

Example:

a inner join b

table primary ::=

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

make dep options ::=

• ( <lparen> ( MAX <colon> <unsigned integer> )? ( JOIN )? <rparen> )?

options for the make dep hint

Example:

(min:10000)

xml serialize ::=

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

array table ::=

• 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

text table ::=

• TEXTTABLE <lparen> <common value expression> ( SELECTOR <string> )? COLUMNS <text table column> ( <comma> <text table column> )* ( NO ROW DELIMITER )? ( DELIMITER <character> )? ( ( ESCAPE <character> ) | ( QUOTE <character> ) )? ( HEADER ( <unsigned integer> )? )? ( SKIP <unsigned integer> )? <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

text table column ::=

• <identifier> <data type> ( WIDTH <unsigned integer> ( NO TRIM )? )? ( SELECTOR <string> <unsigned integer> )?

A text table column.

Example:

x INTEGER WIDTH 6

xml query ::=

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

object table ::=

• 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

object table column ::=

• <identifier> <data type> <string> ( DEFAULT <expression> )?

object table column.

Example:

y integer 'teiid_row_number'

xml table ::=

• 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

xml table column ::=

• <identifier> ( ( FOR ORDINALITY ) | ( <data type> ( DEFAULT <expression> )? ( PATH <string> )? ) )

XML table column.

Example:

y FOR ORDINALITY

unsigned integer ::=

• <unsigned integer literal>

An unsigned interger value.

Example:

12345

table subquery ::=

• ( TABLE | LATERAL )? <lparen> ( <query expression> | <call statement> ) <rparen> ( AS )? <identifier>

A table defined by a subquery.

Example:

(SELECT * FROM tbl) AS x

table name ::=

• ( <identifier> ( ( AS )? <identifier> )? )

A table named in the FROM clause.

Example:

tbl AS x

where clause ::=

• WHERE <condition>

Specifies a search condition

Example:

WHERE x = 'a'

condition ::=

• <boolean value expression>

A boolean expression.

boolean value expression ::=

• <boolean term> ( OR <boolean term> )*

An optionally ORed boolean expression.

boolean term ::=

• <boolean factor> ( AND <boolean factor> )*

An optional ANDed boolean factor.

boolean factor ::=

• ( NOT )? <boolean primary>

A boolean factor.

Example:

NOT x = 'a'

boolean primary ::=

• ( <common value expression> ( <between predicate> | <match predicate> | <like regex predicate> | <in predicate> | <is null predicate> | <quantified comparison predicate> | <comparison predicate> )? )

• <exists predicate>

A boolean predicate or simple expression.

Example:

col LIKE 'a%'

comparison operator ::=

• <eq>

• <ne>

• <ne2>

• <lt>

• <le>

• <gt>

• <ge>

A comparison operator.

Example:

=

comparison predicate ::=

• <comparison operator> <common value expression>

A value comparison.

Example:

= 'a'

subquery ::=

• <lparen> ( <query expression> | <call statement> ) <rparen>

A subquery.

Example:

(SELECT * FROM tbl)

quantified comparison predicate ::=

• <comparison operator> ( ANY | SOME | ALL ) <subquery>

A subquery comparison.

Example:

= ANY (SELECT col FROM tbl)

match predicate ::=

• ( NOT )? ( LIKE | ( SIMILAR TO ) ) <common value expression> ( ESCAPE <character> | ( <lbrace> ESCAPE <character> <rbrace> ) )?

Matches based upon a pattern.

Example:

LIKE 'a_'

like regex predicate ::=

• ( NOT )? LIKE_REGEX <common value expression>

A regular expression match.

Example:

LIKE_REGEX 'a.*b'

character ::=

• <string>

A single character.

Example:

'a'

between predicate ::=

• ( NOT )? BETWEEN <common value expression> AND <common value expression>

A comparison between two values.

Example:

BETWEEN 1 AND 5

is null predicate ::=

• IS ( NOT )? NULL

A null test.

Example:

IS NOT NULL

in predicate ::=

• ( NOT )? IN ( <subquery> | ( <lparen> <common value expression> ( <comma> <common value expression> )* <rparen> ) )

A comparison with multiple values.

Example:

IN (1, 5)

exists predicate ::=

• EXISTS <subquery>

A test if rows exist.

Example:

EXISTS (SELECT col FROM tbl)

group by clause ::=

• GROUP BY ( ROLLUP <lparen> <expression list> <rparen> | <expression list> )

Defines the grouping columns

Example:

GROUP BY col1, col2

having clause ::=

• HAVING <condition>

Search condition applied after grouping.

Example:

HAVING max(col1) = 5

order by clause ::=

• ORDER BY <sort specification> ( <comma> <sort specification> )*

Specifices row ordering.

Example:

ORDER BY x, y DESC

sort specification ::=

• <sort key> ( ASC | DESC )? ( NULLS ( FIRST | LAST ) )?

Defines how to sort on a particular expression

Example:

col1 NULLS FIRST

sort key ::=

• <expression>

A sort expression.

Example:

col1

integer parameter ::=

• <unsigned integer>

• <unsigned value expression primary>

A literal integer or parameter reference to an integer.

Example:

?

limit clause ::=

• ( LIMIT <integer parameter> ( <comma> <integer parameter> )? )

• ( OFFSET <integer parameter> ( ROW | ROWS ) ( <fetch clause> )? )

• <fetch clause>

Limits and/or offsets the resultant rows.

Example:

LIMIT 2

fetch clause ::=

• FETCH ( FIRST | NEXT ) ( <integer parameter> )? ( ROW | ROWS ) ONLY

ANSI limit.

Example:

FETCH FIRST 1 ROWS ONLY

option clause ::=

• 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

expression ::=

• <condition>

A value.

Example:

col1

common value expression ::=

• ( <numeric value expression> ( <concat_op> <numeric value expression> )* )

Establishes the precedence of concat.

Example:

'a' || 'b'

numeric value expression ::=

• ( <term> ( <plus or minus> <term> )* )

Example:

1 + 2

plus or minus ::=

• <plus>

• <minus>

The + or - operator.

Example:

+

term ::=

• ( <value expression primary> ( <star or slash> <value expression primary> )* )

A numeric term

Example:

1 * 2

star or slash ::=

• <star>

• <slash>

The * or / operator.

Example:

/

value expression primary ::=

• <non numeric literal>

• ( <plus or minus> )? ( <unsigned numeric literal> | ( <unsigned value expression primary> ( <lsbrace> <numeric value expression> <rsbrace> )* ) )

A simple value expression.

Example:

+col1

parameter reference ::=

• <qmark>

• ( <dollar> <unsigned integer> )

A parameter reference to be bound later.

Example:

?

unescapedFunction ::=

• ( ( <text aggreate function> | <standard aggregate function> | <ordered aggreate function> ) ( <filter clause> )? ( <window specification> )? )

• ( <analytic aggregate function> ( <filter clause> )? <window specification> )

• ( <function> ( <window specification> )? )