Level 1 Provider SQL Grammar

In Level 1 provider SQL grammar, all operations are on a single table, and dynamic parameters are not supported. This grammar can be converted into postfix notation (meaning that functions are preceded by all their operands) to facilitate the implementation of a simpler, stack-based SQL parser in the data provider.

The following table lists Level 1 provider SQL grammar.

StatementMeaning
Select-statement ::=
SELECT [ALL|DISTINCT] select-list

FROM table-reference-list

[WHERE search-condition]

[GROUP BY column-name [, column-name]]

[HAVING search-condition]

[order-by-clause]

Select-list ::=
* | select-sublist[,select-sublist]
Select-sublist ::=
Expression | Expression aliasname | Expression AS aliasname
Expression ::=
Term|expression {+|-} term
Term ::=
Factor | term {*|/} factor
Factor ::=
[+|-] primary
Primary ::=
Column-name |

Literal |

Realnumber |

Scalar |

USER |

Date |

Time |

Timestamp |

AggregateFunctionTerm |

(expression)

Literal ::=
Character-string-literal
Character-string-literal ::=
'{character}...'
Character ::=
Character set being used
Scalar ::=
ScalarEscape | ScalarShorthand
ScalarEscape ::=
--(*VENDOR(MICROSOFT),PRODUCT(ODBC) FN fn *)--
ScalarShorthand ::=
{ FN fn }
Fn ::=
Functionname (valuelist) | Functionname ( )
AggregateFunctionTerm ::=
COUNT (*) | AVG (expression) | MAX (expression) | MIN (expression) |

SUM (expression)

Column-name ::=
[table-name.]column-identifier
Column-identifier ::=
[table-name.]column-identifier
User-defined-name ::=
Letter[digit|letter|_]...
Table-reference-list ::=
Table-reference [, Table-reference]
Table-reference ::=
Table-name | Outerjoin
Outerjoin::=
Ojescape | Ojshorthand
Ojescape::=
--(*VENDOR(MICROSOFT),PRODUCT(ODBC) OJ Oj*)--
Ojshorthand::=
{ OJ Oj }
Oj::=
Table-name LEFT OUTER JOIN table-name ON Boolean-term |

Table-name LEFT OUTER JOIN Oj ON Boolean-term

Table-name ::=
[TableQualifier.]Table-identifier | [TableQualifier.]Table-identifier aliasname
Table-identifier ::=
Aliasname
TableQualifer ::=
Fully qualified namespace path in double quotes (for example, "root\default" )
Search-condition ::=
Boolean-term [OR search-condition]
Boolean-term ::=
Boolean-factor [AND Boolean-term]
Boolean-factor ::=
[NOT]Boolean-primary
Boolean-primary ::=
Predicate | (search-condition)
Predicate ::=
Comparison-predicate |

Like-predicate |

Null-predicate |

In-predicate |

Exists-predicate

Comparison-predicate ::=
Expression comparison-operator expression |

Expression comparison-operator [ |ALL|ANY] Select-statement

Comparison-operator ::=
<|>|<=|>=|=|<>
Like-predicate ::=
Expression [NOT] LIKE Pattern-value
Pattern-value ::=
Character-string-literal | USER
In-predicate ::=
[NOT] IN (Valuelist) |

[NOT] IN (Select-statement)

Exists-predicate ::=
EXISTS (Select-statement)
Null-predicate ::=
Column-name IS [NOT] NULL
Realnumber ::=
A non-negative real number (including E notation)
Date ::=
DateEscape | DateShorthand
DateEscape ::=
--(* VENDOR(MICROSOFT), PRODUCT(ODBC) d Dateval *)--
DateShorthand ::=
{d Dateval }
Dateval ::=
A date in yyy-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, '1992-02-05 10:19:48.529')

Aliasname ::=
User-defined name
Order-by-clause ::=
ORDER BY sort-specification[,sort-specification]
Sort-specification ::=
{unsigned-integer | column} [ASC |DESC]

 

Windows Server 2003:  The WMI Open Database Connectivity (ODBC) Adapter is not available in Windows Server 2003. When you upgrade to Windows Server 2003, the WMI ODBC Adapter is not removed if it already exists on a computer. For an alternative type of data access, see replace:Microsoft Data Access Components (MDAC).

 

 

Show: