SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-19
Considerations for SEQUENCE BY
°
To use a sequence function as a grouping column, you must use a derived
table for the SEQUENCE BY query and use the derived column in the GROUP
BY clause:
SELECT ordernum, movsum_qty, AVG(unit_price)
FROM
(SELECT ordernum, MOVINGSUM(qty_ordered, 3), unit_price
FROM odetail
SEQUENCE BY partnum)
AS tab2 (ordernum, movsum_qty, unit_price)
GROUP BY ordernum, movsum_qty;
°
To use an aggregate function as the argument to a sequence function, you
must also use a derived table:
SELECT MOVINGSUM(avg_price,2)
FROM
(SELECT ordernum, AVG(unit_price)
FROM odetail
GROUP BY ordernum)
AS tab2 (ordernum, avg_price)
SEQUENCE BY ordernum;
•
Like aggregate functions, sequence functions generate an intermediate result. If
the query has a WHERE clause, its search condition is applied during the
generation of the intermediate result. Therefore, you cannot use sequence
functions in the WHERE clause of a SELECT statement.
°
This query returns an error:
SELECT ordernum, partnum, RUNNINGAVG(unit_price)
FROM odetail
WHERE ordernum > 800000 AND RUNNINGAVG(unit_price) > 350
SEQUENCE BY qty_ordered;
°
Apply a search condition to the result of a sequence function, use a derived
table for the SEQUENCE BY query, and use the derived column in the
WHERE clause:
SELECT ordernum, partnum, runavg_price
FROM
(SELECT ordernum, partnum, RUNNINGAVG(unit_price)
FROM odetail
SEQUENCE BY qty_ordered)
AS tab2 (ordernum, partnum, runavg_price)
WHERE ordernum > 800000 AND runavg_price > 350;