Extensions for Accessing Network-Defined Records
This article describes the following information:
This article describes the following information:
Expansion of Extended-Search Condition
Expansion of Extended-search Condition specifies criteria used to select rows from tables.
Expansion of extended-search-condition
The following example shows the syntax for expansion of extended-search-condition:
►►─┬─search-condition───┬─┬──────────────────────────────────────┬──────────►◄ └─set-specification──┘ │ ┌──────────────────────────────────┐ │ └─▼── AND ──┬─search-condition───┬─┴─┘ └─set-specification──┘
The following list are the parameters for expansion of extended-search-condition:
- search-conditionSpecifies a search condition whose value must be true for the row or rows to be included in the result table.
- set-specificationSpecifies that only rows participating as owner and member in the named set be included in the result table. For expandedset-specificationsyntax, see Expansion of Set-specification Statement.
The full search condition is satisfied when the value of all its operands are true. It is not satisfied when the value of any of its operands is either false or unknown.
Order of Evaluation
CA IDMS effectively evaluates from left to right after first evaluating each operand individually.
You can use parentheses to override the default order of evaluation. Operands in parentheses are evaluated first.
Expansion of Set-Specification Statement
Expansion of the set-specification statement specifies join criteria for tables representing owner and member records of a network-defined set.
Expansion of set-specification
►►─┬─────────┬─set-name──┬─────────────────────────────────┬────────────────►◄ ├─FIRST─┤ │ ┌─────────────────────────────┐ │ └─LAST──┘ └─▼── . ─┬─table-identifier─┬─┴─┘ └─alias────────────┘
The following list are the parameters for expansion of set-specification:
- set-nameSpecifies the name of the set to be used as the test criteria.Set-name must follow the rules for identifiers. If hyphens appear in the name, it must be enclosed in double quotes.
- table-identifierSpecifies the name of a table representing either the owner or member of the set.Table-identifiermust appear in the FROM parameter of the containing query specification or SELECT statement.At most, two table names or aliases can qualify the set name and if both appear, one must identify the owner and the other must identify a member of the set.
- aliasSpecifies the alias assigned to the table representing the owner or member of the set.If the table has been assigned an alias in the FROM parameter of the query specification or SELECT statement whereset-specificationappears, the alias and not the original table name must be used to qualify the set.
- FIRSTSpecifies only the first member record occurrence from each occurrence ofset-nameis returned in the join.
- LASTSpecifies only the last member record occurrence from each occurrence ofset-nameis returned in the join. For chained sets, this command is only valid when the set linkage includes prior pointers.For more information about coding considerations and set linkage, see Set.
Members without Foreign Keys
Joining rows from different tables specified in a SELECT statement is usually done with comparison operations on column values. The most typical approach for SQL-defined tables is to use equal comparisons of the matching primary/foreign key columns of a referential constraint definition.
However, in a network-defined database, member records may not contain the key values of their owner records. For example, it is not necessary for the EMPLOYEE record to contain the department ID of its associated DEPARTMENT record if the relationship between the EMPLOYEE and DEPARTMENT records is represented by a set.
In such cases, column-based comparison cannot be used to process a join; instead, the SELECT statement must identify the set in the WHERE parameter using
A system-owned index is not a set joining two records; therefore, it cannot be used in the WHERE clause.
Two table rows satisfy the
set-specificationcriteria if one is a member of the other in the named set. The value of the set-specification is considered
truewhen this condition is satisfied and
The tables representing the owner and member records must appear in the FROM parameter of the containing query specification or SELECT statement.
Inclusion of the
set-specificationfalse for all member occurrences except for the first or last, respectively. This additional syntax is included for use with sets with an inherent first-in-first-out or last-in-first-out organization.
Set-namemust be qualified under the following conditions:
- More than one member of a multi-member set has been named as a table in the preceding FROM parameter
- A table representing either the owner or a member has been assigned an alias in the preceding FROM parameter
Improved Efficiency of Join Operations
A network-defined member record can contain the value of its owner's key. However, unless the set definition in the network-defined schema identifies this as a foreign key, CA IDMS will not use the set in its access strategy when performing join operations. This may result in the choice of a less-than-optimal access strategy.
This can be overcome by using
set-specificationas part of the selection criteria.
For information about table procedures, see Defining and Using Table Procedures.