Application Note for QuNect ODBC for QuickBase

SQL Syntax Supported by QuNect ODBC for QuickBase

statement ::= CREATE create |  DROP drop | SELECT select orderby | INSERT insert | DELETE delete | UPDATE update 

create ::= TABLE tablename ( createcols ) | INDEX indexname ON tablename ( indexcolumn ) 
    
indexcolumn ::= columnname asc 
  
createcols ::= createcol , createcols | createcol 
  
createcol ::= columnname datatype | columnname datatype ( integer ) | columnname datatype ( integer , integer ) 

drop ::= TABLE tablename
 
select ::= selectcols FROM tablelist where groupby having 
  
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   
  
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 
  
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 
  
indexname ::= 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')