The expansion of query-specification represents a table to be used in the evaluation of an SQL statement.
Expansion of query-specification
►►─── SELECT ─┬────────────┬──────────────────────────────────────────────────► ├─ ALL ◄ ───┤ └─ DISTINCT ─┘ ►─┬─ * ──────────────────────────────────────────────────────┬───────────────► │ ┌─────────────────────── , ───────────────────────────┐ │ └─▼─┬── value-expression ──┬──────────────────────────┬─┴──┤ │ └─┬──────┬───result-name ──┘ │ │ └─ AS ─┘ │ ├─ table-name.* ───────────────────────────────────────┤ └─ alias.* ────────────────────────────────────────────┘ ┌────────────────── , ──────────────────────────────┐ ►─── FROM ─▼─┬─ table-reference─────┬─┬──────────┬─────────┬───┴─────────────► └─ (query-expression) ─┘ └─┬──────┬─┴─ alias ─┘ └─ AS ─┘ ►─┬─────────────────────────────────────────┬────────────────────────────────► └─ WHERE ─┬─ search-condition ──────────┬─┘ └─ extended-search-condition ─┘ ►─┬──────────────────────────────┬───────────────────────────────────────────► └─ PRESERVE ─┬─ table-name ──┬─┘ └─ alias ───────┘ ►─┬────────────────────────────────────────────────┬─────────────────────────► │ ┌────────────── , ────────────────┐ │ └─ GROUP BY ─▼┬┬───────────────┬─ column-name ─┴─┤ │├─ table-name. ─┤ │ │└─ alias. ──────┘ │ └── rowid-pseudo-column ───────────┘ ►─┬───────────────────────────┬──────────────────────────────────────────────► └─ HAVING search-condition ─┘ ►─┬─────────────────────────────┬────────────────────────────────────────────►◄ └─ OPTIMIZE FOR literal ROWS ─┘
Directs CA IDMS to return all the rows, including duplicates, in the requested result table. ALL is the default when you specify neither ALL nor DISTINCT.
Directs CA IDMS to eliminate duplicate rows from the result table of the query specification.
Specifies that the result table is to include all columns in the tables, views, procedures and table procedures named in the FROM parameter of the query specification. The columns in the tables, views, procedures and table procedures are concatenated in the order in which the tables and views are specified in the FROM parameter.
Identifies the values to be included in a result column. Typically, value-expression includes a column reference.
Each column reference in value-expression must identify a column in a table named in the FROM parameter of the query specification.
The number of columns in a result table is the same as the number of value expressions in the query specification defining the result table. For expanded value-expression syntax, see Expansion of Value-expression.
Specifies a name for the result column identified by value-expression. Result-name must be a 1- through 32-character name that follows the conventions for SQL identifiers.
Specifies that the result table is to include all columns in the table identified by table-name.
Table-name must match an occurrence of table-name in the FROM parameter.
Specifies that the result table is to include all columns in the table identified by alias.
Alias must match an occurrence of alias in the FROM parameter.
Identifies one or more tables, views, procedures or table procedures from which the result table is to be derived. For expanded table-reference syntax, see Expansion of Table-reference.
Represents a table to be used in the evaluation of an SQL statement.
Defines a new name to be used to identify the table, view, procedure or table procedure within the query specification. Alias must be a 1- through 18-character name that follows the conventions for SQL identifiers.
Introduces criteria that a row must meet to be included in the result table.
Specifies the set of values against which a row is tested:
For expanded search-condition syntax, see Expansion of Search-condition.
Specifies a search condition that includes a set specification. For expanded extended-search-condition syntax, see Expansion of Extended-search Condition.
Requests an outer join on the specified table, view, or table procedure. The PRESERVE parameter is a CA IDMS extension of the SQL standard.
To specify a more powerful outer join that is compatible with the SQL standard, use the joined-table construct as table-reference.
Specifies by name the table, view, procedure or table procedure to be preserved in an outer join. For expanded table-name syntax, see Expansion of Table-name.
Specifies the table, view, procedure or table procedure to be preserved in an outer join by the alias defined for the table, view, procedure or table procedure in the FROM parameter of the query specification.
Groups the rows in the table defined by the FROM and WHERE parameters by the values in the specified columns. Rows with the same value in each grouping column are grouped together.
Column-name must identify a column in a table, view, procedure or table procedure named in the FROM parameter of the query specification. Multiple column names must be separated by commas.
Specifies the table, view, procedure or table procedure includes the named column. For expanded table-name syntax, see Expansion of Table-name.
Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. Alias must be defined in the FROM parameter of the query specification.
Specifies a pseudo-column ROWID to be used as a grouping column. See Expansion of rowid-pseudo-column for more information.
Specifies criteria a group must meet to be included in the result table:
For expanded search-condition syntax, see Expansion of Search-condition.
Specifies the expected number of output rows from this query-specification. It is used by the optimizer to generate the best possible access strategy for satisfying query-expression. The string literal is an integer constant. The OPTIMIZE FOR parameter is a CA IDMS extension of the SQL standard.
Outer Join Using PRESERVE
Within query-specification, PRESERVE can be used to request an outer join on one of the tables, views, procedures or table procedures named in the FROM parameter. If PRESERVE is specified, the result table includes rows of the preserved table for which no matching row exists in the other tables used in the join operation.
If no matching row exists, the corresponding columns in the result table are set to null. Predicates in the WHERE clause other than those used to perform the outer join are evaluated before determining whether a matching row exists.
The following statement returns the names of all active employees. The name of the employee's spouse is also returned if found. The logic of the statement is that the result table includes the name of each active employee, and whether the employee has a spouse:
select e.first_name, e.last_name,
s.first_name, s.last_name
from employee e, relation s
where e.empid=s.empid
and e.status='A' -- active employee
and s.relationship='S' -- employee's spouse
preserve e ;
Note: Outer join and many other join types can be specified to be compatible with the SQL standard using the joined-table construct in table-reference. See Expansion of Table-reference, for more information.
PRESERVE and Column Order
When using PRESERVE and specifying "*" as the result column list, the order of the columns in the result table depend on which table is being preserved. The columns of the preserved table are always first.
Value Expressions without Column References
If the value expression that identifies a result column does not include any column references, the result column contains the same value in each row. This value is derived directly from the value expression without reference to the table defined by the FROM parameter of the query specification.
Uniqueness of Table References
Each alias and each table reference without an associated alias must be unique within the FROM parameter of a query specification.
Column References in the WHERE Parameter
Each column reference directly included in the search condition in the WHERE parameter of a query specification must unambiguously identify a column in a table, view, procedure or table procedure specified in the FROM parameter of the query specification, or must be an outer reference.
Note: For information about outer references, see Subqueries.
GROUP BY Parameter Requirements
When a query specification includes the GROUP BY parameter, each column reference in the value expressions that identify the result columns must either identify a column specified in the GROUP BY parameter or occur only in the argument of an aggregate function. If the result columns are identified by an asterisk (*), the GROUP BY parameter must include all the columns in the tables, views, and table procedures specified in the FROM parameter.
Query Specifications without the GROUP BY Parameter
If a query specification does not include the GROUP BY parameter and any column reference in a value expression that identifies a result column is included in the argument of an aggregate function:
Column References in the HAVING Parameter
Each column reference included in the search condition in the HAVING parameter of a query specification must either identify a column specified in the GROUP BY parameter of the query specification, occur in the argument of an aggregate function, or be an outer reference.
When to Use OPTIMIZE FOR Literal ROWS
Under some circumstances, the SQL optimizer may choose a less than optimal access strategy to satisfy a query expression. This typically happens with host program embedded SQL statements which contain WHERE clauses with host variable references, rather than explicit constants. For example, a BETWEEN clause involving host variables may induce the optimizer to assume many rows will be retrieved, causing it to choose an area sweep to satisfy the request. Without knowing the underlying values of the host variables, the optimizer cannot know if the BETWEEN will always qualify a small number of rows, thus possibly making an index retrieval much more efficient. The OPTIMIZE FOR literal ROWS clause is used to override the number of expected rows deduced by the optimizer. This allows it to generate better access strategies.
In a CREATE VIEW Statement
The following CREATE VIEW statement defines a view derived from three tables:
create view former_employee
as select e.emp_id, emp_fname, emp_lname,
job_title, start_date, finish_date
from employee e, job j, position p
where e.emp_id = j.emp_id
and e.emp_id = p.emp_id
and finish_date is not null;
In an INSERT Statement
The following INSERT statement inserts rows into the TEMP_EMP_SKILL table.
insert into temp_emp_skill
select emp.emp_id, dept_id, skill_name, skill_level
from employee emp, expertise exp, skill s
where emp.emp_id = exp.emp_id
and exp.skill_id = s.skill_id;
|
Copyright © 2014 CA.
All rights reserved.
|
|