Grammar Element Definitions

---

The following is an alphabetical summary of the element definitions used in the grammar syntax section:

alter-option-list ::=alter-option 
|(alter-option [, alter-option}...) 

 
alter-option ::= ADD [ COLUMN ] column-definition 
| ADD table-constraint-definition 
| DROP [ COLUMN ] column-name 
| DROP CONSTRAINT constraint-name 
| DROP PRIMARY KEY 

 
as-or-semicolon ::= AS | ; 
 
before-or-after ::= BEFORE | AFTER 
 
call-arguments ::= positional-argument [ , positional-argument ]... 
 
col-constraint ::= NOT NULL 
| UNIQUE 
| PRIMARY KEY 
| REFERENCES table-name [ ( column-name ) ] [ referential-actions ] 
 

collation-name ::= 'string' | user-defined-name 
 
column-constraint ::= [ CONSTRAINT constraint-name ] col-constraint 
 
column-definition ::= column-name data-type [ DEFAULT default-value ] [ column-constraint [ column-constraint ]... [CASE | COLLATE collation-name ] 
 
column-name ::= user-defined-name 
 
commit-statement ::= see COMMIT statement 
 
comparison-operator ::= < | > | <= | >= | = | <> 
 
constraint-name ::= user-defined-name 
 
correlation-name ::= user-defined-name 
 
cursor-name ::= user-defined-name 
 
data-type ::= data-type-name [ (precision [ , scale ] ) ] 
 
data-type-name ::= see Pervasive.SQL Supported Data Types 
 
db-name ::= user-defined-name 
 
default-value ::= literal 
 
expression ::= expression - expression 
| expression + expression 
| expression * expression 
| expression / expression 
| ( expression ) 
| -expression 
| +expression 
| column-name 
| ? 
| literal 
| set-function 
| scalar-function 
| { fn scalar-function } 
| USER 
| IF ( search-condition , expression , expression ) 
| SQLSTATE 
| : user-defined-name 
|@:IDENTITY 
|@:ROWCOUNT 
|@@IDENTITY 
|@@ROWCOUNT 

 
expression-or-subquery ::= expression | ( query-specification ) 
 
fetch-orientation ::= | NEXT 
 
group-name ::= user-defined-name 
 
index-definition ::= ( index-segment-definition [ , index-segment-definition ]... ) 
 
index-segment-definition ::= column-name [ ASC | DESC ] 
 
index-name ::= user-defined-name 
 
ins-upd-del ::= INSERT | UPDATE | DELETE 
 
insert-values ::= values-clause  
|  query-specification 

 
join-definition ::= table-reference [ INNER ] JOIN table-reference ON search-condition 
| table-reference CROSS JOIN table-reference 
| outer-join-definition 

 
label-name ::= user-defined-name 
 
literal ::= 'string' 
| number 
| { d 'date-literal' } 
| { t 'time-literal' } 
| { ts 'timestamp-literal' } 
 

order-by-expression ::= expression [ CASE | COLLATE collation-name ] [ ASC | DESC ] 
 
outer-join-definition ::= table-reference outer-join-type JOIN table-reference 
ON search-condition 
 
outer-join-type ::= LEFT [ OUTER ]| RIGHT [ OUTER ] | FULL [ OUTER ] 
 
parameter ::= parameter-type-name data-type [ DEFAULT proc-expr | = proc-expr ] 
| SQLSTATE 

 
parameter-type-name ::= parameter-name 
| parameter-type parameter-name 
| parameter-name parameter-type 

 
parameter-type ::= IN | OUT | INOUT | IN_OUT 
 
parameter-name ::= [ : ] user-defined-name 
 
password ::= user-defined-name | 'string' 
 
positional-argument ::= expression 
 
precision ::= integer 
 
predicate ::= expression [ NOT ] BETWEEN expression AND expression 
| expression comparison-operator expression-or-subquery 
| expression [ NOT ] IN ( query-specification ) 
| expression [ NOT ] IN ( value [ , value ]... ) 
| expression [ NOT ] LIKE value 
| expression IS [ NOT ] NULL 
| expression comparison-operator ANY ( query-specification ) 
| expression comparison-operator ALL ( query-specification ) 
| EXISTS ( query-specification ) 

 
proc-expr ::= same as normal expression but does not allow IF expression, or ODBC-style scalar functions 
 
proc-search-condition ::= same as normal search-condition, but does not allow any expression that includes a subquery. 
 
proc-stmt ::=	  [ label-name : ] BEGIN [ ATOMIC ] [ proc-stmt [ ; proc-stmt ]... ] END [ label-name ] 
| CALL procedure-name ( proc-expr [ , proc-expr ]... ) 
| CLOSE cursor-name 
| DECLARE cursor-name CURSOR FOR select-statement [ FOR UPDATE | FOR READ ONLY ] 
| DECLARE variable-name data-type [ DEFAULT proc-expr | = proc-expr ] 
| DELETE WHERE CURRENT OF cursor-name 
| delete-statement 
| FETCH [ fetch-orientation [ FROM ] ] cursor-name [ INTO variable-name [ , variable-name ] ] 
| IF proc-search-condition THEN proc-stmt [ ; proc-stmt ]... [ ELSE proc-stmt [ ; proc-stmt ]... ] END IF 
| insert-statement 
| LEAVE label-name 
| [ label-name : ] LOOP proc-stmt [ ; proc-stmt ]... END LOOP [ label-name ] 
| OPEN cursor-name 
| PRINT proc-expr [ , 'string' ] 
| RETURN [ proc-expr ] 
| transaction-statement 
| select-statement-with-into 
| select-statement 
| SET variable-name = proc-expr 
| SIGNAL [ ABORT ] sqlstate-value 
| START TRANSACTION 
| update-statement 
| UPDATE SET column-name = proc-expr [ , column-name = proc-expr ]... WHERE CURRENT OF cursor-name 
| [ label-name : ] WHILE proc-search-condition DO [ proc-stmt [ ; proc-stmt ] ]... END WHILE [ label-name ] 
| alter-table-statement   
| create-index-statement   
| create-table-statement   
| create-view-statement   
| drop-index-statement   
| drop-table-statement   
| drop-view-statement   
| grant-statement   
| revoke-statement   
| set-statement  

 
procedure-name ::= user-defined-name 
 
public-or-user-group-name ::= PUBLIC | user-group-name 
 
query-specification [ [ UNION [ ALL ] query-specification ]... 
[ ORDER BY order-by-expression [ , order-by-expression ]... ] 
 
query-specification ::= ( query-specification ) 
| SELECT [ ALL | DISTINCT ] select-list [ table-expression ] 

 
referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ] correlation-name ] 
| NEW [ AS ] correlation-name [ OLD [ AS ] correlation-name ] 

 
referential-actions ::= referential-update-action [ referential-delete-action ] 
| referential-delete-action [ referential-update-action ] 
 

referential-update-action ::= ON UPDATE RESTRICT 
 
referential-delete-action ::= ON DELETE CASCADE 
| ON DELETE RESTRICT 

 
release-statement ::= see RELEASE statement 
 
result ::= user-defined-name data-type 
 
rollback-statement ::= see ROLLBACK WORK statement 
 
savepoint-name ::= user-defined-name 
 
scalar-function ::= see Scalar Function list 
 
scale ::= integer 
 
search-condition ::= search-condition AND search-condition 
| search-condition OR search-condition 
| NOT search-condition 
| ( search-condition ) 
| predicate 

 
select-item ::= expression [ [ AS ] alias-name ] | table-name . * 
 
select-list ::= * | select-item [ , select-item ]... 
 
set-function ::= COUNT (*) 
| COUNT ( [ DISTINCT | ALL ] expression ) 
| SUM ( [ DISTINCT | ALL ] expression ) 
| AVG ( [ DISTINCT | ALL ] expression ) 
| MIN ( [ DISTINCT | ALL ] expression ) 
| MAX ( [ DISTINCT | ALL ] expression ) 

 
sqlstate-value ::= 'string' 
 
table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint 
 
table-constraint ::= UNIQUE (column-name [ , column-name ]... ) 
| PRIMARY KEY ( column-name [ , column-name ]... ) 
| FOREIGN KEY ( column-name [ , column-name ] ) 
REFERENCES table-name 
[ ( column-name [ , column-name ]... ) ] 
[ referential-actions ] 

 
table-element ::= column-definition 
| table-constraint-definition 

 
table-expression ::= 
FROM table-reference [ , table-reference ]... 
[ WHERE search-condition ] 
[ GROUP BY expression [ , expression ]... 
[ HAVING search-condition ]  

 
table-name ::= user-defined-name 
 
table-privilege ::= ALL 
|  SELECT [ ( column-name [ , column-name ]... ) ] 
|  UPDATE [ ( column-name [ , column-name ]... ) ] 
|  INSERT [ ( column-name [ , column-name ]... ) ] 
|  DELETE 
|  ALTER 
|  REFERENCES 

 
table-reference ::= { OJ outer-join-definition } 
| [db-name.]table-name [ [ AS ] alias-name ] 
| join-definition 
| ( join-definition ) 

 
transaction-statement ::= commit-statement 
| rollback-statement 
| release-statement 

 
trigger-name ::= user-defined-name 
 
user-password ::= user-name [ : ] password 
 
user-group-name ::= user-name | group-name 
 
user-name ::= user-defined-name 
 
value ::= literal | USER | ? 
 
value-list ::= ( value [ , value ]... ) 
 
values-clause ::= DEFAULT VALUES | VALUES ( expression [ , expression ]... ) 
 
variable-name ::= user-defined-name 
 
view-name ::= user-defined-name 

SQL Statement List

SqlStatementList is defined as:

SqlStatementList 
Statement ';' | SqlStatementList ';' 
Statement ::= StatementLabel ':' Statement  
|BEGIN ... END block  
|CALL Statement  
|CLOSE CURSOR Statement  
|COMMIT Statement  
|DECLARE CURSOR Statement  
|DECLARE Variable Statement  
|DELETE Statement  
|FETCH Statement  
|IF Statement  
|INSERT Statement  
|LEAVE Statement  
|LOOP Statement  
|OPEN Statement  
|PRINT Statement  
|RELEASE SAVEPOINT Statement  
|RETURN Statement  
|ROLLBACK Statement  
|SAVEPOINT Statement  
|SELECT Statement  
|SET Statement  
|SIGNAL Statement  
|START TRANSACTION Statement  
|UPDATE Statement  
|WHILE Statement 

Predicate

A predicate is defined as:

Expression CompareOperator Expression  
|Expression [ NOT ] BETWEEN Expression AND Expression 
|Expression [ NOT ] LIKE StringLiteral 
|Expression IS [ NOT ] NULL 
|NOT Predicate  
|Predicate AND Predicate 
|Predicate OR Predicate  
|'(' Predicate ')'CompareOperator ::= '=' | '>=' | '>' | '<=' | '<' | '<>' 
|[ NOT ] IN value-list  

Expression

An expression is defined as:

Number  
|StringLiteral  
|ColumnName  
|VariableName  
|NULL  
|CONVERT '(' Expression ',' DataType ')'  
|'-' Expression  
|Expression '+' Expression  
|Expression '-' Expression  
|Expression '*' Expression  
|Expression '/' Expression  
|FunctionName '(' [ ExpressionList ] ')'  
|'(' Expression')'  
|'{' D StringLiteral '}'  
|'{' T StringLiteral '}'  
|'{' TS StringLiteral '}' 
|@:IDENTITY 
|@:ROWCOUNT 
|@@IDENTITY 
|@@ROWCOUNT 

An expression list is defined as:

ExpressionList ::= Expression [ , Expression ... ]