Commands

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.

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

Searches using ASSIGNVALUES are not optimized for MDK access.

ASSIGNVALUES is most useful when testing installations using OdxSQL.

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

SET AUTOFETCH OFF

Syntax

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

ASSIGNVALUES
Required

value | (value[,value...])
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 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

Top