DI-ODBC SQL Grammar Quick Reference

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')