DI-ODBC supports SELECT statements with FROM, but not SELECT DISTINCT or UNION. Also, some operators and functions are not supported. Here is a specification of the grammar supported by DI-ODBC driver:
<select-statement>
SELECT <select-columns> FROM <table-ref> [ WHERE <expression> ] [ GROUP BY <group-columns> [ HAVING <expression>] ] [ ORDER BY <order-columns> ] [ LIMIT <number> ] [ OFFSET <number> ]
As of DI-ODBC 7.1(7), this becomes:
SELECT <select-columns> [ FROM <table-ref> ] [ WHERE <expression> ] [ GROUP BY <group-columns> [ HAVING <expression>] ] [ ORDER BY <order-columns> ] [ LIMIT <number> ] [ OFFSET <number> ]
<select-columns>
<select-column> [ , <select-columns> ]
<select-column>
<column-ref> [ AS <name> ]
| <aggregate> [ AS <name> ]
As of DI-ODBC 7.1(7), this becomes:
<expression> [ AS <name> ]
<group-columns>
<column-ref> [ , <group-columns> ]
As of DI-ODBC 7.1(25), <group-columns> includes both column aliases and original column names.
<order-columns>
<order-column> [ , <order-columns> ]
<order-column>
<column-ref> [ ASC | DESC ]
<table-ref>
[ <name> . ] [ <name> . ] <name>
As of DI-ODBC 7.1(7), table reference includes:
| <select-statement> [ AS <name> ]
<column-ref>
[ <name> . ] <name>
<expression>
<literal>
| <column-ref>
| <operator>
| ( <expression> )
| <function>
<literal>
<number>
| <string>
| NULL
As of DI-ODBC 7.1(25), literal reference includes:
| <escaped-date>
| <escaped-timestamp>
| '?'
<escaped-date>
{ d <string> }
(string is of the form 'YYYY-MM-DD')
<escaped-timestamp>
{ ts <string> }
(string is of the form 'YYYY-MM-DD' hh:mm:ss')
<operator>
<expression> + <expression>
| <expression> - <expression>
| <expression> * <expression>
| <expression> / <expression>
| <expression> = <expression>
| <expression> == <expression>
| <expression> <> <expression>
| <expression> != <expression>
| <expression> < <expression>
| <expression> <= <expression>
| <expression> > <expression>
| <expression> >= <expression>
| <expression> AND <expression>
| <expression> OR <expression>
| <expression> || <expression>
| NOT <expression>
| - <expression>
| <expression> ISNULL
| <expression> IS NULL
| <expression> IS NOT NULL
As of DI-ODBC 7.1(7), operators include:
| <expression> IN ( [ <in-expressions> ] )
| <expression> NOT IN ( [ <in-expressions> ] )
| <expression> BETWEEN <expression> AND <expression>
As of DI-ODBC 7.1(25), expressions include:
| <expression> + <interval-expression>
| <expression> - <interval-expression>
<in-expressions> (As of DI-ODBC 7.1(25))
<expression> [ , <expression> ]
<function>
<math-function>
| <string-function>
| <time-function>
| <misc-function>
| <aggregate>
| <di-function>
As of DI-ODBC 7.1(25), functions include:
| <escaped-function>
<math-function>
ABS( <expression> )
| ACOS( <expression> )
| ASIN( <expression> )
| ATAN( <expression> )
| CEILING( <expression> )
| COS( <expression> )
| COT( <expression> )
| DEGREES( <expression> )
| EXP( <expression> )
| FLOOR( <expression> )
| LOG( <expression> )
| LOG10( <expression> )
| MOD( <expression> )
| PI()
| POWER( <expression> )
| RADIANS( <expression> )
| RAND()
| SIGN( <expression> )
| SIN( <expression> )
| SQRT( <expression> )
| TAN( <expression> )
<string-function>
LOWER( <expression> )
| LCASE( <expression> )
| UPPER( <expression> )
| UCASE( <expression> )
| SUBSTRING( <expression>, <expression>, <expression> )
| CONCAT( <expression>, <expression> )
| LENGTH( <expression> )
<time-function>
CURRENT_DATE()
| CURDATE()
| NOW()
| DAYNAME( <expression> )
| MONTHNAME( <expression> )
| DAYOFMONTH( <expression> )
| MONTH( <expression> )
| YEAR( <expression> )
As of DI-ODBC 71.(25), time-functions include:
| EXTRACT( <time-unit> FROM <expression>)
| TIMESTAMP 'YYYY-MM-DD hh:mm:ss'
<time-unit>
YEAR
| MONTH
| DAY
<misc-function>
IFNULL( <expression>, <expression> )
| COALESCE( <expression>, <expression> )
<aggregate>
COUNT(*)
| COUNT(<expression>)
| COUNT(DISTINCT <expression>)
| SUM(<expression>)
| MIN(<expression>)
| MAX(<expression>)
| AVG(<expression>)
| STDEV(<expression>)
<di-function>
CALC(<expression>)
As of DI-ODBC 7.1(25), di-function includes:
| MEASURE(<expression>)
| EVAL(<string>)
<escaped-function> (As of DI-ODBC 7.1(25))
{ fn <function> }
<name>
a bare identifier, for example my_column_name
| a double-quoted identifier, for example "my column name"
<string>
a single-quoted string, for example 'my string'
(use two single-quotes to represent one inside the string, for example 'William ''The Bard'' Shakespeare')