Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

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

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 inner join b

joined table ::=

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

A table or join.

Example:

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

A single source of rows.

Example:

xml serialize ::=

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:

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

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

Select Your Language

Language:

Language:

Red Hat Training

A.7. Productions

Quick Links

Help

Site Info

Related Sites

About

Red Hat legal and privacy links

Red Hat legal and privacy links

Language:

Language and Page Formatting Options

Language:

Red Hat Training

A.7. Productions

Quick Links

Help

Site Info

Related Sites

Systems Status

About

Red Hat legal and privacy links

Red Hat legal and privacy links