This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | |||
|
dev:sql:assignvalues [2009/11/30 05:43] tdo |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| - | ====== Omnidex SQL: ASSIGNVALUES ====== | ||
| - | ===== Description ===== | ||
| - | |||
| - | **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. FIXME | ||
| - | |||
| - | 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 | ||
| - | ===== Syntax ===== | ||
| - | |||
| - | ASSIGNVALUES <value | (value[,value...])> | ||
| - | [TO label] | ||
| - | [ON [CURSOR] cursor] | ||
| - | |||
| - | ===== Discussion ===== | ||
| - | |||
| - | == <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. | ||
| - | ===== Examples ===== | ||
| - | 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. | ||
| - | <code> | ||
| - | > set AUTOFETCH=OFF | ||
| - | |||
| - | > select company, contact from customers where state in $values(2,,,'VAL1') | ||
| - | |||
| - | > assignvalues ('CO','CA') to VAL1 | ||
| - | |||
| - | > fetch all | ||
| - | </code> | ||
| - | 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. | ||
| - | <code> | ||
| - | > 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 | ||
| - | </code> | ||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||