QUERY expression (named query expression) (SAP Library

QUERY expression (named query expression)

QUERY expressions ( named query expressions ) are required to generate an unordered result table in a SELECT statement (named select statement).

Syntax

<named query expression> ::= <named query term> | <named query expression> UNION [ALL] <query term> | <named query expression> EXCEPT [ALL] <query term>

named query term, query term

Explanation

If the QUERY expressions consists of more than one QUERY specification (query spec) (specified in named query term or in query term ), the only the first QUERY specification in the QUERY expression may be a QUERY specification ( named query spec ).

If the QUERY expressions consists of only one QUERY specification (named query spec) (specified in named query term ), the result of the expression is the unchanged result of the QUERY specification.

If a QUERY expression consists of more than one QUERY specification, the number of selected columns in all QUERY specifications of the QUERY expression must be the same. The respective i^th selected columns of the QUERY specifications must be comparable.

Column type (select column)
Numeric columns	Are comparable. If all i^th selected columns are numeric columns, the i ^th column of the result table is a numeric column.
Alphanumeric columns, code attribute BYTE	Are comparable.
Alphanumeric columns, code attribute ASCII, EBCDIC	Are comparable. Are also comparable with date, time, and timestamp values.
All i^th columns are date v values	The i^th column of the result table is a date value.
All i^th columns are time values	The i^th column of the result table is a time value.
All i^th columns are timestamp values	The i^th column of the result table is a timestamp value.
Columns of the type BOOLEAN	Are comparable.
All i^th columns are of the type BOOLEAN	The i^th column of the result table is of the type BOOLEAN.
Columns of any other data type (not mentioned above)	The i^th column of the result table is an alphanumeric column. Comparable columns with differing code attributes are converted.

If columns are comparable but have different lengths, the corresponding column of the result table has the maximum length of the underlying columns.

Column names in the result table

The names of the result table columns are formed from the names of the selected columns of the first QUERY specification.

Let T1 be the left operand of UNION, EXCEPT, or INTERSECT (defined in named query term ). Let T2 be the right operand. Let R be the result of the operation on T1 and T2.

A row is a duplicate of another row if both have identical values in each column. NULL values (see data type) are assumed to be identical. Special NULL values are assumed to be identical.

UNION: R contains all rows of T1 and T2.

EXCEPT: R contains all rows from T1 which have no duplicate rows in T2.

INTERSECT: R contains all rows from T1 which have a duplicate row in T2. A row from T2 can only be a duplicate row of exactly one row from T1. More than one row from T1 cannot have the same duplicate row in T2.

DISTINCT is implicitly assumed for the query expressions belonging to T1 and T2 if ALL is not specified. All duplicate rows are removed from R.

If parentheses are missing, then INTERSECT will be evaluated before UNION and EXCEPT. UNION and EXCEPT have the same precedence and will be evaluated from left to right in the case that parentheses are missing.