ODBC: Supported SQL Grammar

Supported SQL Grammar

The grammar declarations are described with these notations:

Notation	Description
<blank>	No qualifier is required
<command>	Use one of the specified commands
[qualifier]	Optional qualifier, may be omitted
(paramval)	Required parameter value
expression	Numeric or string calculation

statement::= ALTER [alter-] | CREATE [create-] | DELETE [delete-] | DROP [drop-] | INSERT [insert-] | GRANT [grant-] | RECITAL [recital-] | REVOKE [revoke-] | SELECT [select-] | UPDATE [update-]

aggterm	AVG ([expression-]) | MAX ([expression-]) | MIN ([expression-]) | MIN ([expression-]) | SUM ([expression-])
alias	AS [aliasname-]
aliasname	[identifier-]
alter	TABLE [tablename-] ADD ([createcols-])
and	[not-] AND [and-]
asc	ASC | DESC
boolean	[and-] OR [boolean-]
coldesc	DESCRIPTION [string-]
colref	[columnname-]
column	[columnname-]
columnlist	[identifier-]
columnname	[identifier-]
comparison	[colref-] IS NULL | [colref-] IS NOT NULL | [expression-] LIKE [pattern-] | [expression-] NOT LIKE [pattern-] | [expression-] IN {[valuelist-]} | [expression-] NOT IN {[valuelist-]} | [expression-] [op-] [expression-]
create	0 [UNIQUE] INDEX [indexname-] ON [tablename-] ([indexcols-])
createcol	[columnname-] [datatype-] ([integer-]) [coldesc-] | [columnname-] [datatype-] ([integer-], [integer-]) [coldesc-]
createcols	[createcol-]
cursorname	[identifier-]
datatype	VARCHAR | LONG VARCHAR | DECIMAL | NUMERIC | SMALLINT | INTEGER | REAL | FLOAT | DOUBLE PRECISION | BIT | TINYINT | SHORT | DATE | LOGICAL | MEMO | BYTE | CURRENCY | DATETIME | LONG VARBINARY | ZONED
date	a date in ODBC escape clause format (for example, {d'1996-04-04'} or --(*vendor(Microsoft), product(ODBC)d'1996-04-04'*)--
delete	FROM [tablename-] [where-]
drop	INDEX [indexname-]
expression	[expression-] - [times-] | [times-]
forupdate	FOR UPDATE | FOR UPDATE OF [columnlist-]
grant	[privilege-] ON [tablename-] TO [usergroups-]
groupby	GROUP BY [groupbyterms-]
groupbyterms	[colref-], [groupbyterms-]
groups	[integer-] | [integer-] – [integer-], [groups-] | [integer-]– [integer-] | *
having	HAVING [boolean-]
identifier	an identifier (identifiers containing spaces must be enclosed in double quotes)
indexcols	DESC] | [indexcols-], [indexcols-]
indexname	[identifier-]
insert	INTO [tablename-] [insertvals-]
insertvals	VALUES ([valuelist-])
integer	a non-negative integer
join	OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | CROSS JOIN [tableref-] ON [table-].[column-] = [table-].[column-]
neg	+ [term-] | - [term-]
not	NOT [comparison-]
op	>= | < | <= | = | <>
orderby	ORDER BY [orderbyterms-]
orderbyterm	[integer-] [asc-]
orderbyterms	[orderbyterm-], [orderbyterms-]
pattern	? | USER
privilege	ALTER | DELETE | INSERT | READ ONLY [columnlist-] | SELECT [columnlist-] | UPDATE [columnlist-]
realnumber	a non-negative real number
recital	any recital command excluding user interface commands
revoke	[privilege-] ON [tablename-] FROM [usergroups-]
select	[selectcols-] FROM [tablelist-] [where-] [groupby-] [having-] [orderby-] [forupdate-]
selectallcols	ALL | DISTINCT
selectcols	[selectallcols-] [selectlist-]
selectlist	[expression-]
set	[column-] = [simpleterm-]
setlist	[setlist-], [set-]
simpleterm	[realnumber-] | ? | USER | [date-] | [time-] | [timestamp-]
string	a string (enclosed in single quotes)
table	[aliasname-]
tablelist	[tableref-] | [tableref-] [join-] | [viewname-]
tablename	[identifier-]
tableref	[tablename-] [alias-]
term	[colref-] | [simpleterm-] | [aggterm-]
time	a time in ODBC escape clause format (for example,{t'10:19:48'} or --(*vendor(Microsoft), product(ODBC)t'10:19:48'*)--
times	[times-] / [neg-] | [neg-]
timestamp	a timestamp in ODBC escape clause format (for example, {ts'1996-04-04 10:19:48.529'} or --(*vendor(Microsoft), product(ODBC)ts'1996-04-04 10:19:54.529'*)
update	[tablename-] SET [setlist-] [where-]
usergroup	user and group access control string in the format '[users,groups]'
usergroups	PUBLIC
users	[integer-] | [integer-] – [integer-] , [users-] | [integer-] – [integer-] | *
valuelist	[expression-], [valuelist-] | [expression-] | NULL
viewname	[identifier-]
where	WHERE [boolean-] | WHERE CURRENT OF [cursorname-]