WITH Options

The WITH clause is provided in the Omnidex Query language to allow an application to set options for a statement that are specific to that statement.

The WITH clause is appended to the end of an SQL statement, following the optional ON CURSOR|INSTANCE n clause, and contains a comma (or space) separated list of options that are to be applied to the processing of the SQL statement.

EXPORT COMPANY, CONTACT, PHONE FROM CUSTOMERS WHERE STATE='CO' WITH CHAR=AUTO

Syntax

statement [ON [CURSOR] n | [INSTANCE] n] [WITH option1[, option2]]

statement

Required. Any valid SQL statement supported by Omnidex.

ON [CURSOR | INSTANCE] n

Optional. See ON CURSOR ON INSTANCE for more details.

WITH

Required if specifying options to be used for this current statement. Omit otherwise.

options

Required if specifying options to be used for the current statement. Omit otherwise. This is a comma separated list of options valid for the current statement.

OdxSQL Example

Options passed in the WITH clause will be applied to all appropriate underlying OA API routines. For example, an EXPORT statement passed through oaexecsql will also cause calls to oaselect and oaexport. If the CHAR option is passed in the WITH clause of the SQL statement in oaexecsql, it will be applied to both oaselect and oaexport.

EXPORT COMPANY, CONTACT, PHONE FROM CUSTOMERS WHERE STATE='CO' TO CUSTS.TXT WITH CHAR=AUTO

It is possible to control which underlying functions a particular option is applied to. For example, the above example applies the CHAR option to both oaselect and oaexport, by default. However, this option can be applied to only one of these underlying functions by appending the function name, with a colon, to the front of the option, as shown:

EXPORT COMPANY, CONTACT, PHONE FROM CUSTOMERS WHERE STATE='CO' TO CUSTS.TXT WITH OAEXPORT:CHAR=AUTO

In this case, CHAR=AUTO is applied to oaexport but not oaselect.

Each of the underlying OA API routines support a different set of options.

Top