A query specification specifies a combination of a derived query expression and the query expression itself in the WITH clause. When the WITH clause is used, the derived table produced by the derived query expression can be the query name, which can be specified in the query expression itself.
The query expression body specifies either a query specification or a set operation in order to determine the union set or the disjunction set between the derived tables that are obtained as a result of a query specification. UNION [ALL] must be specified when a union set is to be determined. EXCEPT [ALL] must be specified when a disjunction set is to be determined. A query expression can be specified in either a cursor declaration or a cursor specification in a dynamic SELECT statement.
query-expression::=[WITH query-name[(column-name[,column-name]...)]
AS(derived-query-expression)
[, query-name[(column-name[,column-name]...)]
AS(derived-query-expression)]...]
query-expression-body
derived-query-expression::= query-expression-body
query-expression-body::= {query-specification
| (query-expression-body)
| query-expression-body {UNION | EXCEPT} [ALL]
{query-specification | (query-expression-body) }}
derived-query-expression::=query-expression-body
Specify a table expression, a column to be derived as a query name, a set operation, or whether duplicate-row exclusion is on. In the FROM clause contained in a table expression in query-expression-body, specify either a table name or a view table name.
When the WITH clause is used, specifies a table name, a view table name, or a query name in the FROM clause contained in the table expression in the query expression body.
When the WITH clause is used, the following items cannot be specified in a query expression body: an alias table defined for access to a table in another RD node, a table in another RD node, or a view table in another RD node.
When the WITH clause is used, a single-row SELECT statement cannot be specified in the query expression body.
Specifies the table expressions, the columns to be selected, or whether or not duplicates elimination is to be in effect for the rows to be selected.
See 2.3 Query specification for details about query specifications.
Specifies the order in which the set operations in the query expressions are to be evaluated when two or more query specifications joined by UNION or EXCEPT are specified (query-expression-body must be specified in parentheses).
Specifies that the union set or the difference set of derived tables produced by two query specifications or query expression bodies is to be determined. For details about set operations, see (6) below.
WITH QRY1 (QC1,QC2) AS (SELECT MAX(C1+C2),AVG(C1+C2)
FROM T1),
QRY2 (QC1,QC2) AS (SELECT C1+C2,C3-C4 FROM T2)
SELECT * FROM QRY1 X
WHERE QC1>(SELECT QC1 FROM QRY2 WHERE QC2 = X.QC2)
WITH QRY1 (QC1) AS (SELECT COUNT(*) FROM T1),
QRY2 (QC1,QC2) AS (SELECT C1+C2,C3/C4 FROM T2)
SELECT * FROM QRY1 WHERE EXISTS
(SELECT * FROM QRY2 WHERE QC1 = QRY1.QC1)
Operand 1 | Operand 2 | ||||
---|---|---|---|---|---|
SMALLINT | INTEGER | DECIMAL | SMALLFLT | FLOAT | |
SMALLINT | SMALLINT | INTEGER | DECIMAL | SMALLFLT | FLOAT |
INTEGER | INTEGER | INTEGER | DECIMAL | FLOAT | FLOAT |
DECIMAL | DECIMAL | DECIMAL | DECIMAL | FLOAT | FLOAT |
SMALLFLT | SMALLFLT | FLOAT | FLOAT | SMALLFLT | FLOAT |
FLOAT | FLOAT | FLOAT | FLOAT | FLOAT | FLOAT |
Set operation | ALL not specified | ALL specified |
---|---|---|
UNION | min (1, n+m) | m+n |
EXCEPT | 1 (m>0 and n=0) 0 (m=0 or n>0) | max (m-n, 0) |
DECLARE CR1 CURSOR FOR
WITH QRY1(QPCODE,QPNAME,QCOLOR,QSALES) AS
(SELECT PCODE,PNAME,COLOR,PRICE*SQUANTITY FROM STOCK),
SELECT QPNAME,MAX(QSALES) FROM QRY1 GROUP BY QPNAME
DECLARE CR1 CURSOR FOR
SELECT PCODE,PNAME,COLOR,PRICE,SQUANTITY
FROM STOCK