This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
dev:sql:assignvalues [2009/11/30 02:30] tdo created |
— (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][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 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 | ||
| - | |||
| - | ===== Discussion ===== | ||
| - | |||
| - | == arg1 == | ||
| - | |||
| - | == arg2 == | ||
| - | |||
| - | ===== Examples ===== | ||
| - | |||
| - | ==== Simple ==== | ||
| - | |||
| - | ==== 2nd example ==== | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||