Home

Getting Started

Utilities

Indexing

Omnidex

Development

Tutorials

Quick Links

 

Appendix

$VALUES

Syntax

Examples

 

Appendix

$VALUES is a pointer to a buffer containing some undetermined set of values for use as criteria in a call to oaselect, or in a SQL SELECT statement WHERE clause predicate through oaexecsql or any Omnidex application that uses the SQL language interface.

$VALUES can contain one or more values in array format, which are then assigned to a particular select, using oaassignvalues for an oaselect or ASSIGNVALUES for oaexecsql and applications using the SQL language interface.

The values contained in $VALUES can be results from a previous query, dbms native key values, or some other query criteria that is usually undetermined prior to executing the program.

The column that the criteria is applied to should be an Omnidex indexed column, otherwise a serial read will be performed for each value in $VALUES.

$VALUES can be used in boolean and set inclusion operations like column in $VALUES or equations like column=$VALUES.

 

Syntax

$VALUES[([number],['type'],[length],['label'])]

number
Optional. number is an integer that indicates the maximum number of values that will be stored in $VALUES. Default is 1.

type
Optional. type is a quoted string declaring the data type of the values being passed to $VALUES. This defaults to the data type defined in the environment catalog for the column being equated to $VALUES. See datatype_spec for a list of data types that can be entered here.

length
Optional. length is an integer that indicates the length of each element in $VALUES. This defaults to the length defined in the environment catalog for the column being equated to $VALUES.

label
Optional. label is a quoted string that uniquely identifies a particular $VALUES expression. This allows multiple $VALUES expressions to be used in a single oaselect or SQL SELECT WHERE clause.

 

Examples

OASELECT / OAASSIGNVALUES

This example will return a single record: the company, contact and phone columns from the customers table for a row with an account number of 1234.

OASELECT(0,"CHAR",STATUS,"CUSTOMERS","COMPANY,CONTACT,PHONE",
"ACCOUNT_NO=$VALUES")

OAASSIGNVALUES(0,,STATUS,,1,1234)

OASELECT - 0 is the cursor number, "CHAR" is the option applied to the select, STATUS is a reference to the Status Array, "CUSTOMERS" is the table to retrieve the data from, "COMPANY,CONTACT,PHONE" are the columns to retrieve, "ACCOUNT_NO IN $VALUES" is the criteria.

OAASSIGNVALUES - 0 is the cursor number, "CHAR" is the option applied to oaassignvalues, STATUS is a reference to the Status Array, no label is applied for this $VALUES token, 1 value is contained in $VALUES, 1234 is the value contained in $VALUES.

OAEXECSQL / ASSIGNVALUES

OAEXECSQL(1,,STATUS,"SELECT company, contact, phone FROM customers
WHERE account_no in $VALUES(20,'CHAR',12,'ACCT')")

OAEXECSQL(1,,STATUS,"ASSIGNVALUES ('1234','1235','1236',...,'1253') TO ACCT ON 1")

OAEXECSQL - 1 is the cursor number, there are no options being applied to this SELECT, STATUS is a reference to the Status Array, "SELECT ..." and "ASSIGNVALUES ..." are the statements being executed in each OAEXECSQL function.

$VALUES - 20 is the maximum number of elements in the array reference by $VALUES, "CHAR" is the option being applied to the values in $VALUES, 12 is the length, in bytes, of each $VALUES value, "ACCT" is the label assigned to the $VALUES symbol.

ASSIGNVALUES - ('1234','1235'...) are the values being used as criteria against the account_no column, TO ACCT assigns these values to the $VALUES token labeled "ACCT", ON 1 indicates the cursor number to execute the ASSIGNVALUES function on.

SQL SELECT / ASSIGNVALUES

SELECT * FROM prospects WHERE state=$VALUES(,,,'Colorado') AND city=$VALUES(3,,,'Cities')

ASSIGNVALUES 'CO' TO Colorado

ASSIGNVALUES ('Boulder','Denver','Longmont') TO Cities

 

 

 

Top