The parameters used in the expansion of the Set-specification statement specify join criteria for tables representing owner and member records of a non-SQL-defined set.
Expansion of set-specification
►►─┬─────────┬─ set-name ──┬─────────────────────────────────┬────────────────►◄ ├─ FIRST ─┤ │ ┌─────────────────────────────┐ │ └─ LAST ──┘ └─▼── . ─┬─ table-identifier ─┬─┴─┘ └─ alias ────────────┘
Specifies 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.
Specifies the name of a table representing either the owner or member of the set. Table-identifier must 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.
Specifies 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 where set-specification appears, the alias and not the original table name must be used to qualify the set.
Specifies only the first member record occurrence from each occurrence of set-name is returned in the join.
Specifies only the last member record occurrence from each occurrence of set-name is returned in the join. For chained sets, this command is only valid when the set linkage includes prior pointers.
Note: For more information about coding considerations and set linkage, see Chapter 3.3, Sets in the CA IDMS Navigational DML Programming Guide.
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 non-SQL-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 set-specification.
Note: A system-owned index is not a set joining two records; therefore, it cannot be used in the WHERE clause.
Evaluation
Two table rows satisfy the set-specification criteria if one is a member of the other in the named set. The value of the set-specification is considered true when this condition is satisfied and false otherwise.
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 FIRST and LAST keywords renders set-specification false 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.
Qualification Requirements
Set-name must be qualified under the following conditions:
Improved Efficiency of Join Operations
A non-SQL-defined member record can contain the value of its owner's key. However, unless the set definition in the non-SQL-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-specification as part of the selection criteria.
|
Copyright © 2014 CA.
All rights reserved.
|
|