OASELECT
oaselect defines tables, columns and a select expression, then selects
rows against tables or data entities in an environment catalog for subsequent
retrieval by oafetch or transfer by oaexport.
A call to oaselect prepares for either a serial retrieval, a native indexed
retrieval, or a retrieval against a subset of rows previously qualified
by the oaqualify routine. It defines which type of retrieval should be
performed, which select items to return, and any additional criteria that
must be met. A subsequent call to:
- oafetch initiates the retrieval of rows defined by oaselect.
- oaexport transfers the rows defined by oaselect.
The criteria parameter contains SQL-like expressions that indicate the
type of retrieval to perform, and what additional criteria must be met.
For example, if criteria contains the string STATE='CA', oaselect only
selects rows where the STATE column contains the characters “CA”.
When called, oafetch then returns those rows.
Syntax
oaselect (cursor, options, status, tables, columns, criteria)
cursor -- Is a 32-bit signed integer
passed by value. This is the value returned by a successful call to oaopencursor.The
next call to oafetch that references cursor initiates the search defined
by the oaselect call.
options -- Is a 256-byte character string,
passed by reference, that indicates a special action for oaselect to take.
Separate multiple options with commas, and terminate the option list with
a semicolon or a null character.
status -- Indicates the success or failure
of the oaselect routine. A zero status.error means a successful call to
oaselect. The status structure is passed by reference and contains fourteen
32-bit signed integers, followed by a 36-character buffer.
tables -- Contains the name of the table
or tables that contain the columns you want to retrieve and optionally,
aliases for those table names. Table is a character value, up to 4096
bytes, passed by reference, and terminated by a semicolon or null character.
Separate multiple table names with commas and terminate the array with
a semicolon or a null character.
You can only select from multiple tables when the underlying SQL database
supports this directly.
columns -- Is a 4096-byte character value,
passed by reference that contains the select items to retrieve. Separate
multiple items with commas and terminate the list with a semicolon or
a null character.
Columns referenced in criteria need not appear in columns, but must be
valid columns in the table referenced in table. Columns may contain an
asterisk ( * ) to retrieve all columns in the table, some expressions
and also variables representing data entities in an environment catalog.
criteria -- The search criteria for retrieving
rows. Criteria is a character value, up to 8192 bytes long, passed by
reference, and terminated with a null character or a semicolon. It can
contain a WHERE, ORDER BY or GROUP BY clause of an SQL SELECT statement,
and various operators.
Criteria must be empty when options contains OPTIMIZATION=SERIAL.
Options
BACKWARD_READ -- accesses the native
database in reverse order. Note: This option has no relation to backward
reading of OMNIDEX or IMSAM indexes.
BUFFER -- lists selected rows in the
default row-by-row format.
CHAIN_COUNT -- causes each subsequent
oafetch to return the number of child rows associated with each selected
parent row. Note: Only supported for DBMSs that provide these features.
CHAR=n -- returns binary values in character
format, where n is the byte length of the input number. The default is
32 bytes. The alphanumeric representation of the values is right-justified,
space filled to length n.
COLUMN -- returns the selected data as
column-oriented instead of row-oriented.
MAX_ROWS_READ=n -- restricts the number
of rows read (before filtering by OmniAccess) from the native database
management system to a set number of rows ( n). Note: Not supported for
optimized indexed aggregations.
MAX_ROWS_RETURNED=n -- restricts the
number of rows returned (after filtering by OmniAccess), regardless of
the number of rows read, to a set number of rows ( n). Note: Not supported
for optimized indexed aggregations.
If the selection or “filtering” is performed by the native
database management system, as it sometimes is in the case of relational
databases, this option will take more time and require more processing.
OmniAccess only analyzes the select request for valid syntax and a valid
environment file. There is no impact to the underlying data driver and
no access or validation at the native data level. You cannot use this
option before fetching rows.
ODXID -- causes oaselect to select rows
from the specified table using the set of OMNIDEX internal IDs accumulated
by previous calls to oaqualify or oajoin. This is the equivalent of passing
a $ODXID expression in the criteria parameter.
OPTIMIZATION -- sets the selection optimization
for oaselects and oalists using this cursor. Optimizations are set through
an equation:
OPTIMIZATION='ASKQUAL,ASKRETRIEVAL'
The data access optimization you choose for oaselect supersedes any optimization
you may have chosen for the cursor when you opened it with oaopencursor.
REQUIRED_OPTIMIZATION -- The Required
Optimization feature enforces specified optimization rules on a select
statement. In the event that a select statement cannot be optimized according
to those rules, the select will stop processing and return an error. Subsequent
operations against that select such as fetches and exports, will be disallowed.
Required optimizations are set through an equation:
REQUIRED_OPTIMIZATION='NO_MULTIFINDS,NO_UNOPTIMIZED_CRITERIA'
RECNO_ERRORS -- prevents subsequent fetches
from failing if row IDs cannot be mapped to row numbers.
RESTART -- restarts the cursor to the
state it was immediately after the previous select.
Example
Top
|
