Expansion of Query-specification

The expansion of query-specification represents a table to be used in the evaluation of an SQL statement.
idmscu19
The expansion of query-specification represents a table to be used in the evaluation of an SQL statement.
This article describes the following information:
2
2
Syntax
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 ─┘
Parameters
  • ALL
    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.
  • 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. If the schema for the entities named in the FROM parameter was defined using the WITH VIRTUAL KEYS option, the result will also include the ROWID and virtual foreign key pseudo-columns. The virtual foreign key columns are sorted alphabetically.
  • value-expression
    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.
  • AS
    result-name
    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.
  • table-name.*
    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.
  • alias
    .*
    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.
  • FROM table-reference
    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.
  • (query-expression)
    Represents a table to be used in the evaluation of an SQL statement.
  • AS
    alias
    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.
  • WHERE
    Introduces criteria that a row must meet to be included in the result table.
    • search-condition
      Specifies the set of values against which a row is tested:
      • When the value of
        search-condition
        is true, the row is included in the result table
      • When the value of
        search-condition
        is false or unknown, the row is not included in the result table
      For expanded
      search-condition
      syntax, see Expansion of Search-condition.
    • extended-search-condition
      Specifies a search condition that includes a set specification. For expanded
      extended-search-condition
      syntax, see Expansion of Extended-search Condition.
  • PRESERVE
    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
    .
    • table-name
      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.
    • alias
      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.
  • GROUP BY
    column-name
    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.
    • table-name
      Specifies the table, view, procedure or table procedure includes the named column. For expanded
      table-name
      syntax, see Expansion of Table-name.
    • alias
      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.
    • rowid-pseudo-column
      Specifies a pseudo-column ROWID to be used as a grouping column. See Expansion of rowid-pseudo-column for more information.
  • HAVING search-condition
    Specifies criteria a group must meet to be included in the result table:
    • When the value of
      search-condition
      is true, the group is included in the result table
    • When the value of
      search-condition
      is false or unknown, the group is not included in the result table
    For expanded
    search-condition
    syntax, see Expansion of Search-condition.
  • OPTIMIZE FOR literal ROWS
    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.
Usage
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 ;
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.
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:
  • All column references in all the value expressions must be in aggregate functions
  • The entire table defined by the FROM and WHERE parameters is treated as a single group
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.
Examples
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;