Omnidex SQL: ASSIGNVALUES

ASSIGNVALUES is used to assign an undetermined set of values contained in a buffer ($VALUES) to be used as selection criteria in a SQL SELECT statement. The $VALUES buffer can contain a single value or an array of values.

Searches using ASSIGNVALUES are not optimized for MDK access.

ASSIGNVALUES is most useful when testing installations using OdxSQL and is typically not used within ODBC or JDBC applications.

AUTOFETCH must be set to OFF before executing a bind command.

SET AUTOFETCH OFF

ASSIGNVALUES <value | (value[,value...])>
    [TO label]
    [ON [CURSOR] cursor]
    [WITH options]

Required - Either a single value or a comma separated value list enclosed in parenthesis. These are the values to be used as criteria in a SELECT statement.

[TO label] Optional - Give a name to the buffer containing the values to be used as criteria. This is most useful when using more than one set of values.

[ON [CURSOR] cursor] Optional - Specify which cursor is to be used for this command. If omitted, the current cursor will be used. The “CURSOR” keyword is optional.

[WITH options] Optional - Specify options to be used for this command.

Options There are currently no options available to use with ASSIGNVALUES. Support for the WITH options clause was added for future enhancements.

Example In ODXSQL, the AUTOFETCH setting must be set to OFF prior to using ASSIGNVALUES.

set AUTOFETCH=OFF

The flow of calls to use ASSIGNVALUES involves a SQL select statement with one or more $VALUES clauses in the WHERE clause, followed by an ASSIGNVALUES statement for each $VALUES clause in the preceding select, and then a fetch statement.

select company, contact from customers where state in $values(2'VAL1') >assignvalues ('CO','CA') to VAL1 >fetch all A space is required between the ASSIGNVALUES keyword and the value or value list. Multiple $VALUES assignments can be used in a single select by giving each a label. >select company, contact from customers where state in $values(2'VAL1') and city in $values(4,,,'VAL2')

assignvalues ('CO','CA') to VAL1

assignvalues ('BOULDER','DENVER','LOS ANGELES','SAN FRANCISCO') to VAL2

fetch all

Statement