Query Specifications, Subqueries, Query Expressions, and Cursor Specifications › Expansion of Query-expression

Expansion of Query-expression

The expanded parameters of query-expression represent a table to be used in the evaluation of an SQL statement.

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

Note: 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;