SQL Grammar
The PRO/5 SQLPREP verb accepts SQL statements for processing by various SQL engines.
SQL Grammar for Supported Statements
The following defines the SQL grammar for SQL statements supported by the PRO/5 Data Server. SQL reserved words are written in uppercase to make them more readily identifiable.
In BBj 8.0 and higher, REGEXP comparison is syntactically the same as the LIKE comparison except that REGEXP uses standard regular expression patterns instead of the SQL patterns LIKE uses.
|
Syntax |
Statement Type |
Description |
|---|---|---|
|
CREATE INDEX indexname ON tablename( indexcolumns) |
Create Index |
Creates an index of a column or group of columns within a table. An index essentially functions as an ordered list (alphabetical or numeric) that provides a fast and efficient way to look up records. If a UNIQUE index is specified, this indicates an index that does not allow duplicate values. |
|
CREATE TABLE tablename (createcols ) |
Create Table |
Creates a new table in a database by physically creating a data file. Specify the columns (and their data types) wanted to define a record in the table. |
|
CREATE VIEW viewname |
Create View |
Creates a view of one or more tables. Views are typically used to restrict or allow a users access to a database, and only to necessary information. |
|
DELETE FROM tablename where |
Delete |
Deletes one or more records in a table. |
|
DROP INDEX indexname |
Drop Index |
Removes an index from a table in a database. This does not alter the data in any way. |
|
DROP TABLE tablename |
Drop Table |
Removes a table from a database. |
|
DROP VIEW viewname |
Drop View |
Removes a view from a database. |
|
INSERT INTO tablename insertvals |
Insert |
Inserts a new record into a table. By default, you provide the values for all the columns in the record in the order they are defined. You can, however, optionally set only values for a subset of columns, provided you do not omit any columns that are defined as being NOT NULL, since they must be defined when inserting a new record. For duplicate records, Insert returns an error. To replace duplicates, use Replace. |
|
REPLACE INTO tablename insertvals |
Replace |
In BBj 12.0 and higher, this type inserts a new record into a table. By default, you provide the values for all the columns in the record in the order they are defined. You can, however, optionally set only values for a subset of columns, provided you do not omit any columns that are defined as being NOT NULL, since they must be defined when inserting a new record. For duplicate records, Replace does replace the duplicates. To prevent replacement of duplicates, use Insert, which will return an error. |
|
SELECT [TOP integer] selectcols FROM tablelist where groupby having |
Select |
Selects information from one or more tables. This is the most fundamental building block in formulating queries on databases. It does not allow data to be changed, but combined with all of its options, it becomes a powerful query builder. |
|
UPDATE tablename SET setlist where |
Update |
Updates the values of one or more columns in one or more records in a table. |
The following describes syntax items in the left column of the preceding table and their components. See SQL Grammar Definitions for complete definitions of BASIS SQL grammar.
|
Syntax |
Description |
|
aggterm |
aggterm ::= COUNT ( * ) | AVG (expression) | MAX (expression) | MIN (expression) | SUM (expression) |
|
aliasname |
aliasname ::= identifier |
|
and |
and ::= not | not AND and |
|
asc |
asc ::= | ASC | DESC |
|
boolean |
boolean ::= and | and OR boolean |
|
colref |
colref ::= aliasname . columnname | columnname |
|
columnlist |
columnlist ::= columnname , columnlist | columnname |
|
columnname |
columnname ::= identifier |
|
comparison |
comparison ::= ( boolean ) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE pattern | expression REGEXP pattern | expression NOT REGEXP pattern | expression IN ( valuelist ) | expression NOT IN (valuelist) | expression op expression | EXISTS ( SELECT select ) | expression op selectop ( SELECT select) | expression IN ( SELECT select) | expression NOT IN ( SELECT select) |
|
create |
create ::= TABLE tablename ( createcols ) | INDEX indexname ON tablename( indexcolumns ) |
|
createcol |
createcol ::= columnname datatype | columnname datatype( integer ) | columnname datatype(integer, integer) |
|
createcols |
createcols ::= createcol , createcols | createcol |
|
datatype |
datatype ::= identifier |
|
date |
date ::= dateescape | dateshorthand |
|
dateescape |
dateescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) d dateval )*-- |
|
dateshorthand |
dateshorthand ::= { d dateval } |
|
dateval |
dateval ::= a date in yyyy-mm-dd format in single quotes (for example, '1996-02-05') |
|
delete |
delete ::= FROM tablename where |
|
drop |
drop ::= TABLE tablename | INDEX indexname | VIEW viewname |
|
expression |
expression ::= expression + times | expression - times | times |
|
fn |
fn ::= functionname ( valuelist ) | functionname ( ) |
|
functionname |
functionname ::= identifier |
|
groupby |
groupby ::= | GROUP BY groupbyterms |
|
groupbyterms |
groupbyterms ::= colref | colref, groupbyterms |
|
having |
having ::= | HAVING boolean |
|
identifier |
identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotes) |
|
indexcolumn |
indexcolumn ::= columnname asc |
|
indexcolumns |
indexcolumns ::= indexcolumn | indexcolumn, indexcolumns |
|
indexname |
indexname ::= identifier |
|
insert |
insert ::= INTO tablename insertvals |
|
insertvals |
insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES (valuelist) | (columnlist) VALUES ( SELECT select ) | VALUES ( SELECT select) |
|
integer |
integer ::= a non-negative integer |
|
neg |
neg ::= term | + term | - term |
|
not |
not ::= comparison | NOT comparison |
|
oj (not available in PRO/5 or VPRO/5 SQL engine) |
/oj/ ::= tableref LEFT OUTER JOIN /tableref/ ON /boolean/ | /tableref/ LEFT OUTER JOIN /oj/ ON /boolean/ |
|
ojescape (not available in PRO/5 or VPRO/5 SQL engine) |
/ojescape/ ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) OJ oj )*-- |
|
ojshorthand (not available in PRO/5 or VPRO/5 SQL engine) |
ojshorthand ::= { OJ oj } |
|
op |
op ::= > | >= | < | <= | = | <> |
|
orderby |
orderby ::= | ORDER BY orderbyterms |
|
orderbyterm |
orderbyterm ::= colref asc | integer asc |
|
orderbyterms |
orderbyterms ::= orderbyterm | orderbyterm, orderbyterms |
|
outerjoin |
outerjoin ::= ojescape | ojshorthand |
|
pattern |
pattern ::= string | ? | USER |
|
realnumber |
realnumber ::= a non-negative real number (including E notation) |
|
scalar |
scalar ::= scalarescape | scalarshorthand |
|
scalarescape |
scalarescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) FN fn )*-- |
|
scalarshorthand |
scalarshorthand ::= { FN fn } |
|
select |
select ::= selectcols FROM tablelist where groupby having |
|
selectallcols |
selectallcols ::= | ALL | DISTINCT |
|
selectcols |
selectcols ::= selectallcols * | selectallcols selectlist |
|
selectlist |
selectlist ::= selectlistitem , selectlist | selectlistitem |
|
selectlistitem |
selectlistitem ::= expression | expression aliasname | expression AS aliasname |
|
selectop |
selectop ::= | ALL | ANY |
|
set |
set ::= columnname = NULL | columnname = expression |
|
setlist |
setlist ::= set | setlist , set |
|
simpleterm |
simpleterm ::= string | realnumber | ? | USER | date | time | timestamp |
|
string |
string ::= a string (enclosed in single quotes) |
|
tablelist |
tablelist ::= tablelistitem , tablelist | tablelistitem |
|
tablelistitem |
tablelistitem ::= tableref | outerjoin |
|
tablename |
tablename ::= identifier |
|
tableref |
tableref ::= tablename | tablename aliasname |
|
term |
term ::= ( expression ) | colref | simpleterm | aggterm | scalar |
|
time |
time :: = timeescape | timeshorthand (a time in hh:mm:ss format in single quotes, for example, '10:19:48') |
|
timeescape |
timeescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) ttimeval )*-- |
|
times |
times ::= times * neg | times / neg | neg |
|
timeshorthand |
timeshorthand ::= { t timeval } |
|
timestamp |
timestamp ::= timestampescape | timestampshorthand |
|
timestampescape |
timestampescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) ts timestampval )*-- |
|
timestampshorthand |
timestampshorthand ::= { ts timestampval } |
|
timestampval |
timestampval ::= a timestamp in yyyy-mm-dd hh:mm:ss[.ffffff] format in single quotes (for example, '1996-02-05 10:19:48.529') |
|
update |
update ::= tablename SET setlist where |
|
valuelist |
valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL |
|
viewname |
viewname ::= identifier |
|
where |
where ::= | WHERE boolean |