$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
|