Application Note for QuNect ODBC for QuickBase
SQL Syntax Supported by QuNect ODBC for QuickBase
statement ::= ALTER alter | CREATE create | DROP drop | SELECT select orderby | INSERT insert | DELETE delete | UPDATE update alter ::= TABLE tablename ADD createcol create ::= TABLE tablename ( createcols ) createcols ::= createcol , createcols | createcol createcol ::= columnname datatype | columnname datatype ( integer ) | columnname datatype ( integer , integer ) | CONSTRAINT constraintname PRIMARY KEY (columnname) | CONSTRAINT constraintname FOREIGN KEY (columnname) REFERENCES tablename(columnname) drop ::= TABLE tablename select ::= top selectcols FROM tablelist where groupby having limit delete ::= FROM tablename where insert ::= INTO tablename insertvals update ::= tablename SET setlist where setlist ::= set | setlist , set set ::= columnname = NULL | columnname = expression insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES ( valuelist ) | ( columnlist ) SELECT select | SELECT select columnlist ::= columnname , columnlist | columnname valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL top ::= | TOP integer selectcols ::= selectallcols * | selectallcols selectlist selectallcols ::= | ALL | DISTINCT selectlist ::= selectlistitem , selectlist | selectlistitem selectlistitem ::= expression | expression aliasname | expression AS aliasname | aliasname.* where ::= | WHERE boolean having ::= | HAVING boolean limit ::= | LIMIT integer boolean ::= and | and OR boolean and ::= not | not AND and not ::= comparison | NOT comparison comparison ::= ( boolean ) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE 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 ) | expression BETWEEN expression AND expression) | expression NOT BETWEEN expression AND expression) selectop ::= | ALL | ANY op ::= > | >= | < | <= | = | <> pattern ::= string | ? | USER expression ::= expression + times | expression - times | times times ::= times * neg | times / neg | neg neg ::= term | + term | - term term ::= ( expression ) | colref | simpleterm | aggterm | scalar scalar ::= scalarescape | scalarshorthand scalarescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) FN fn )*-- scalarshorthand ::= { FN fn } fn ::= functionname ( valuelist ) | functionname ( ) aggterm ::= COUNT ( * ) | AVG ( expression ) | MAX ( expression ) | MIN ( expression ) | SUM ( expression ) | COUNT ( expression ) simpleterm ::= string | realnumber | ? | USER | date | time | timestamp groupby ::= | GROUP BY groupbyterms groupbyterms ::= colref | colref , groupbyterms orderby ::= | ORDER BY orderbyterms orderbyterms ::= orderbyterm | orderbyterm , orderbyterms orderbyterm ::= colref asc | integer asc asc ::= | ASC | DESC colref ::= aliasname . columnname | columnname tablelist ::= tablelistitem , tablelist | tablelistitem tablelistitem ::= tableref | outerjoin outerjoin ::= ojescape | ojshorthand ojescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) OJ oj )*-- ojshorthand ::= { OJ oj } oj := leftoj | inneroj leftoj ::= tableref | ( leftoj ) | leftoj LEFT OUTER JOIN leftoj ON boolean inneroj ::= tableref | ( inneroj ) | inneroj INNER JOIN inneroj ON boolean tableref ::= tablename | tablename aliasname constraintname ::= identifier functionname ::= identifier tablename ::= identifier datatype ::= identifier columnname ::= identifier aliasname ::= identifier identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotes) string ::= a string (enclosed in single quotes, when used in a LIKE clause the percent sign (%) character is a wildcard and matches any number of characters, the underscore character matches any single character, use a backslash to escape the percent sign and underscore characters) realnumber ::= a non-negative real number (including E notation) integer ::= a non-negative integer date ::= dateescape | dateshorthand dateescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) d dateval )*-- dateshorthand ::= { d dateval } dateval ::= a date in yyyy-mm-dd format in single quotes (for example, '1996-02-05') time ::= timeescape | timeshorthand timeescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) t timeval )*-- timeshorthand ::= { t timeval } timeval ::= a time in hh:mm:ss format in single quotes (for example, '10:19:48') timestamp ::= timestampescape | timestampshorthand timestampescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) ts timestampval )*-- timestampshorthand ::= { ts 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')