Expansion of Query-expression

The expanded parameters of query-expression represent a table to be used in the evaluation of an SQL statement.
idmscu19
The expanded parameters of query-expression represent a table to be used in the evaluation of an SQL statement.
This article describes the following information:
2
2
Syntax
Expansion of query-expression
►►─┬─ 
query-specification
 ─┬──────────────────────────────────────────────────►    └─ (
query-expression
) ──┘  ►─┬──────────────────────────────────────────────────┬───────────────────────►◄    │ ┌──────────────────────────────────────────────┐ │    └─▼─ UNION ─┬───────┬──┬─ 
query-specification
 ─┬─┴─┘                └─ ALL ─┘  └─ (
query-expression
) ──┘
Parameters
  • query-specification
    Represents a table resulting from the evaluation of a query specification. For expanded
    query-specification
    syntax, see Expansion of Query-specification.
  • ( query-expression )
    Specifies another query expression to be used as a single operand in the query expression.
  • UNION all
    Specifies that:
    • The result table is to include the rows from the table represented by the operand preceding the UNION operator and the rows from the table represented by the operand following the UNION operator
    • Duplicate rows are eliminated from the table resulting from the UNION operation, unless the ALL keyword is present.
    The data types and lengths of unioned columns must be compatible. Detailed information is presented under "Usage".
  • ALL
    Specifies that all rows from the UNION operation are retained; duplicates are not discarded.
Usage
Result Data Type
This matrix shows the data type that results when a UNION operation is performed on columns of compatible data types.
    I2 I4 I8 R4 R8 PD ZD UP UZ CH VC BI DT GR VG TI DI     -------------------------------------------------- I2  I2 I4 I8 R4 R8 PD ZD PD ZD -  -  -  -  -  -  TI DI I4  I4 I4 I8 R4 R8 PD ZD PD ZD -  -  -  -  -  -  TI DI I8  I8 I8 I8 R4 R8 PD ZD PD ZD -  -  -  -  -  -  TI DI R4  R4 R4 R4 R4 R8 R4 R4 R4 R4 -  -  -  -  -  -  TI DI R8  R8 R8 R8 R8 R8 R8 R8 R8 R8 -  -  -  -  -  -  TI DI PD  PD PD PD R4 R8 PD PD PD PD -  -  -  -  -  -  TI DI ZD  ZD ZD ZD R4 R8 PD ZD PD ZD -  -  -  -  -  -  TI DI UP  PD PD PD R4 R8 PD ZD UP UZ -  -  -  -  -  -  TI DI UZ  ZD ZD ZD R4 R8 PD ZD UP UZ -  -  -  -  -  -  TI DI CH  -  -  -  -  -  -  -  -  -  CH VC CH -  GR VG -  - VC  -  -  -  -  -  -  -  -  -  VC VC VC -  VG VG -  - BI  -  -  -  -  -  -  -  -  -  CH VC BI -  GR VG -  - DT  -  -  -  -  -  -  -  -  -  -  -  -  DT -  -  -  - GR  -  -  -  -  -  -  -  -  -  CH VC BI -  GR VG -  - VG  -  -  -  -  -  -  -  -  -  VC VC BI -  VG VG -  - TI  TI TI TI TI TI TI TI TI TI -  -  -  -  -  -  TI - DI  DI DI DI DI DI DI DI DI DI -  -  -  -  -  -  -  DI   Key: I2 -- Small integer     BI -- Binary I4 -- Integer           DT -- Date/time I8 -- Long integer      UP -- Unsigned decimal R4 -- Real              UZ -- Unsigned numeric R8 -- Double precision  GR -- Graphic PD -- Decimal           VG -- Vargraphic ZD -- Numeric           TI -- Time interval CH -- Character         DI -- Date interval VC -- Varchar           -  -- Incompatible types
Nullable Columns
If both columns in a UNION operation are not nullable, the result is not nullable; otherwise the result is nullable.
Result Precision
The result precision of decimal, numeric, char, varchar, graphic, vargraphic, and binary is always large enough to hold the larger of the source columns in a UNION operation.
Restrictions on Multiple Query Specifications
If a query expression includes more than one query specification:
  • Result tables returned by the query specifications must all have the same number of columns
  • Columns in any given position in the result tables returned by the query specifications must be compatible for assignment
Updateable Query Expressions
A query expression is updateable under the following conditions:
  • The expression consists of a single query specification (that is, the query expression does not include the UNION operator)
  • The FROM parameter in the query specification specifies only one table, view, procedure or table procedure
  • If a view is named in the FROM parameter, it is updateable
  • The query specification does not contain DISTINCT, PRESERVE, GROUP BY, or HAVING parameters, nor is an aggregate function used in the specification of a result column
For more information about usage considerations for query expressions, see "Usage" under Expansion of Query-specification
Example
In a DECLARE CURSOR Statement
The following DECLARE CURSOR statement creates a cursor for the table resulting from the UNION of two query specifications. The four result columns identified by the second query specification have the same data types, lengths, and null specifications as the four result columns identified by the first query specification.
declare all_curr_emp cursor    for select emp_id, emp_fname, emp_lname, dept_id       from employee       where status <> 'T'       union select con_id, con_fname, con_lname, dept_id          from consultant          where proj_id is not null    order by 4, 1, 2, 3;