A.7. Productions

string ::=

  • < string literal >
A string literal value. Use '' to escape ' in the string.
Example: 
'a string'
'it''s a string'

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 >
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: 
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END

if statement ::=

  • IF < lparen > < condition > < rparen > < statement > ( ELSE < statement > )?
A procedure loop statement that executes over the given cursor.
Example: 
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... 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.

Note

All assignments 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: 
CREATE VIRTUAL PROCEDURE 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.

Note

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.

Note

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 | MAKENOTDEP )?
A single source of rows.
Example: 
a

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 < 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 > )*
Specifies 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 >
  • < qmark >
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 > ( < comma > < identifier > )* | 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 > )
A simple value expression.
Example: 
+col1

unsigned value expression primary ::=

  • < qmark >
  • ( < dollar > < unsigned integer > )
  • ( < escaped function > < function > < rbrace > )
  • ( ( < text aggreate function > | < standard aggregate function > | < ordered aggreate function > ) ( < filter clause > )? ( < window specification > )? )
  • ( < analytic aggregate function > ( < filter clause > )? < window specification > )
  • ( < function > ( < window specification > )? )
  • ( ( < identifier > | < non-reserved identifier > ) ( < lsbrace > < common value expression > < rsbrace > )? )
  • < subquery >
  • ( < lparen > < expression > < rparen > ( < lsbrace > < common value expression > < rsbrace > )? )
  • < searched case expression >
  • < case expression >
An unsigned simple value expression.
Example: 
col1

window specification ::=

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

  • 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

searched case expression ::=

  • 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

function ::=

  • ( CONVERT < lparen > < expression > < comma > < data type > < rparen > )
  • ( CAST < lparen > < expression > AS < data type > < rparen > )
  • ( SUBSTRING < lparen > < expression > ( ( FROM < expression > ( FOR < expression > )? ) | ( < comma > < expression list > ) ) < rparen > )
  • ( EXTRACT < lparen > ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND ) FROM < expression > < rparen > )
  • ( TRIM < lparen > ( ( ( ( LEADING | TRAILING | BOTH ) ( < expression > )? ) | < expression > ) FROM )? < expression > < rparen > )
  • ( ( TO_CHARS | TO_BYTES ) < lparen > < expression > < comma > < string > < rparen > )
  • ( ( TIMESTAMPADD | TIMESTAMPDIFF ) < lparen > < time interval > < comma > < expression > < comma > < expression > < rparen > )
  • < querystring function >
  • ( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT ) < lparen > ( < expression list > )? < rparen > )
  • ( ( TRANSLATE | INSERT ) < lparen > ( < expression list > )? < rparen > )
  • < xml parse >
  • < xml element >
  • ( XMLPI < lparen > ( ( NAME )? < identifier > ) ( < comma > < expression > )? < rparen > )
  • < xml forest >
  • < json object >
  • < xml serialize >
  • < xml query >
  • ( < identifier > < lparen > ( ALL | DISTINCT )? ( < expression list > )? ( < order by clause > )? < rparen > ( < filter clause > )? )
Calls a scalar function.
Example: 
func('1', col1)

xml parse ::=

  • XMLPARSE < lparen > ( DOCUMENT | CONTENT ) < expression > ( WELLFORMED )? < rparen >
Parses the given value as XML.
Example: 
XMLPARSE(DOCUMENT doc WELLFORMED)

querystring function ::=

  • QUERYSTRING < lparen > < expression > ( < comma > < derived column > )* < rparen >
Produces a URL query string from the given arguments.
Example: 
QUERYSTRING(col1 AS opt, col2 AS val)

xml element ::=

  • XMLELEMENT < lparen > ( ( NAME )? < identifier > ) ( < comma > < xml namespaces > )? ( < comma > < xml attributes > )? ( < comma > < expression > )* < rparen >
Creates an XML element.
Example: 
XMLELEMENT(NAME "root", child)

xml attributes ::=

  • XMLATTRIBUTES < lparen > < derived column > ( < comma > < derived column > )* < rparen >
Creates attributes for the containing element.
Example: 
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)

json object ::=

  • JSONOBJECT < lparen > < derived column list > < rparen >
Produces a JSON object containing name value pairs.
Example: 
JSONOBJECT(col1 AS val1, col2 AS val2)

derived column list ::=

  • < derived column > ( < comma > < derived column > )*
a list of name value pairs
Example: 
col1 AS val1, col2 AS val2

xml forest ::=

  • XMLFOREST < lparen > ( < xml namespaces > < comma > )? < derived column list > < rparen >
Produces an element for each derived column.
Example: 
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)

xml namespaces ::=

  • XMLNAMESPACES < lparen > < xml namespace element > ( < comma > < xml namespace element > )* < rparen >
Defines XML namespace URI/prefix combinations
Example: 
XMLNAMESPACES('http://foo' AS foo)

xml namespace element ::=

  • ( < string > AS < identifier > )
  • ( NO DEFAULT )
  • ( DEFAULT < string > )
An xml namespace
Example: 
NO DEFAULT

data type ::=

  • ( STRING ( < lparen > < unsigned integer > < rparen > )? )
  • ( VARCHAR ( < lparen > < unsigned integer > < rparen > )? )
  • BOOLEAN
  • BYTE
  • TINYINT
  • SHORT
  • SMALLINT
  • ( CHAR ( < lparen > < unsigned integer > < rparen > )? )
  • INTEGER
  • LONG
  • BIGINT
  • ( BIGINTEGER ( < lparen > < unsigned integer > < rparen > )? )
  • FLOAT
  • REAL
  • DOUBLE
  • ( BIGDECIMAL ( < lparen > < unsigned integer > ( < comma > < unsigned integer > )? < rparen > )? )
  • ( DECIMAL ( < lparen > < unsigned integer > ( < comma > < unsigned integer > )? < rparen > )? )
  • DATE
  • TIME
  • TIMESTAMP
  • OBJECT
  • ( BLOB ( < lparen > < unsigned integer > < rparen > )? )
  • ( CLOB ( < lparen > < unsigned integer > < rparen > )? )
  • ( VARBINARY ( < lparen > < unsigned integer > < rparen > )? )
  • XML
A data type.
Example: 
STRING

time interval ::=

  • 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
A time interval keyword.
Example: 
SQL_TSI_HOUR

non numeric literal ::=

  • < string >
  • < binary string literal >
  • FALSE
  • TRUE
  • UNKNOWN
  • NULL
  • ( < escaped type > < string > < rbrace > )
An escaped or simple non numeric literal.
Example: 
'a'

unsigned numeric literal ::=

  • < unsigned integer literal >
  • < approximate numeric literal >
  • < decimal numeric literal >
An unsigned numeric literal value.
Example: 
1.234

ddl statement ::=

  • ( < create table > | < create procedure > | < option namespace > | < alter options > | < create trigger > ) ( < semicolon > )?
A data definition statement.
Example: 
CREATE FOREIGN TABLE X (Y STRING)

option namespace ::=

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

  • 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

procedure parameter ::=

  • ( IN | OUT | INOUT | VARIADIC )? < identifier > < data type > ( NOT NULL )? ( RESULT )? ( DEFAULT < string > )? ( < options clause > )?
A procedure or function parameter
Example: 
OUT x INTEGER

procedure result column ::=

  • < identifier > < data type > ( NOT NULL )? ( < options clause > )?
A procedure result column.
Example: 
x INTEGER

create table ::=

  • CREATE ( FOREIGN TABLE | ( VIRTUAL )? VIEW ) < identifier > ( < create table body > | ( < options clause > )? ) ( AS < query expression > )?
Defines a table or view.
Example: 
CREATE VIEW vw AS SELECT 1

create foreign temp table ::=

  • CREATE FOREIGN TEMPORARY TABLE < identifier > < create table body > ON < identifier >
Defines a foreign temp table
Example: 
CREATE FOREIGN TEMPORARY TABLE t (x string) ON z

create table body ::=

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

  • FOREIGN KEY < column list > REFERENCES < identifier > ( < column list > )?
Defines the foreign key referential constraint.
Example: 
FOREIGN KEY (a, b) REFERENCES tbl (x, y)

primary key ::=

  • PRIMARY KEY < column list >
Defines the primary key.
Example: 
PRIMARY KEY (a, b)

other constraints ::=

  • ( ( UNIQUE | ACCESSPATTERN ) < column list > )
  • ( INDEX < lparen > < expression list > < rparen > )
Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.
Example: 
UNIQUE (a)

column list ::=

  • < lparen > < identifier > ( < comma > < identifier > )* < rparen >
A list of column names.
Example: 
(a, b)

table element ::=

  • < identifier > < data type > ( NOT NULL )? ( AUTO_INCREMENT )? ( ( PRIMARY KEY ) | ( ( UNIQUE )? ( INDEX )? ) ) ( DEFAULT < string > )? ( < options clause > )?
Defines a table column.
Example: 
x INTEGER NOT NULL

options clause ::=

  • OPTIONS < lparen > < option pair > ( < comma > < option pair > )* < rparen >
A list of statement options.
Example: 
OPTIONS ('x' 'y', 'a' 'b')

option pair ::=

  • < identifier > ( < non numeric literal > | ( < plus or minus > )? < unsigned numeric literal > )
An option key/value pair.
Example: 
'key' 'value'

alter options ::=

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

alter options list ::=

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

  • DROP < identifier >
drop option
Example: 
DROP updatable

add set option ::=

  • ( ADD | SET ) < option pair >
add or set an option pair
Example: 
ADD updatable true

alter column options ::=

  • ALTER ( COLUMN | PARAMETER )? < identifier > < alter options list >
alters a set of column options
Example: 
ALTER COLUMN bar OPTIONS (ADD updatable true)