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.
AUTOFETCH must be set to OFF before executing a bind command.
SET AUTOFETCH OFF
ASSIGNVALUES <value|(value[,value...])>
[TO label]
[ON [CURSOR] cursor]
ASSIGNVALUES Required
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.
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.
Optional - Specify which cursor is to be used for this command. If omitted, the current cursor will be used. The “CURSOR” keyword is optional.
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') \
(cont) and city in $values(4,,,'VAL2')
> assignvalues ('CO','CA') to VAL1
> assignvalues ('BOULDER','DENVER','LOS ANGELES','SAN FRANCISCO') to VAL2
> fetch all