SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
7-23
HASH_GROUPBY Operator
The HASH_GROUPBY operator has one child node. The description field for this
operator contains the following:
The following is an example of the HASH_GROUPBY operator:
PREPARE TestQuery4 FROM
SELECT l_orderkey,
CAST(SUM(l_extendedprice*(1-l_discount))AS
NUMERIC(18,2)), o_orderdate, o_shippriority
FROM customer,orders,lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
HAVING sum(l_extendedprice)> 100
ORDER BY 2 DESC,3 ASC;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
grouping_columns ....... DETCAT.DETSCH.OX2.O_ORDERDATE,
DETCAT.DETSCH.OX2.O_SHIPPRIORITY,
DETCAT.DETSCH.OX2.O_ORDERKEY
aggregates .............
sum(DETCAT.DETSCH.LX3.L_EXTENDEDPRICE),
sum((cast(DETCAT.DETSCH.LX3.L_EXTENDEDPRICE
AS BIG NUM(12,2) SIGNED) * cast((cast((1 *
100) AS NUMERIC(13,2) SIGNED) -
DETCAT.DETSCH.LX3.L_DISCOUNT) AS BIG NUM(13,
2) SIGNED)))
selection_predicates ...
Token Followed by ... Data Type
fragment_id A sequential number assigned to the fragment. 0
is always the master executor and 1 is reserved
for the EXPLAIN plan. Numbers 2 to n will be
ESP or DAM fragments.
integer
parent_frag The fragment_id for the parent of the current
fragment. The value is (none) for the master
executor.
integer
fragment_type Master, ESP, or DP2. text
aggregates Expression of the aggregate function. expr(text)
selection_predicates Expression of the HAVING clause. expr(text)
grouping_columns Expression of the grouping columns. expr(text)
TP663851.fm Page 23 Thursday, August 2, 2012 4:47 PM