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-60
ROOT Operator
The following is an example of the ROOT operator:
PREPARE TestQuery15 FROM
SELECT CAST(SUM(l_extendedprice*l_discount) AS
NUMERIC(18,2))
AS revenue FROM lineitem WHERE l_shipdate >= DATE '1994-0101'
AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR AND
l_discount BETWEEN .06 - 0.01 AND .06 + 0.01
AND l_quantity < 24;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
xn_access_mode ......... read_only
plan_version ........... 2,200
SCHEMA ................. DETCAT.DETSCH
HASH_JOINS ............. ON
NESTED_JOINS ........... ON
select_list ............
cast(cast((sum((cast(DETCAT.DETSCH.LX3.L_EXTENDEDPRI
CE AS BIG NUM(12,2) SIGNED) *
cast(DETCAT.DETSCH.LX3.L_DISCOUNT
AS BIG NUM(12,2) SIGNED))) /
cast(100 AS BIG NUM(3) SIGNED)) AS
NUMERIC(18,2) SIGNED) AS
NUMERIC(18,2) SIGNED)
statement_index Statement index of this statement as reported
by the Measure product.
integer
upd_action_on_error Determines the type of statement atomicity
chosen for a query:
XN_ROLLBACK: Transaction is rolled back if
an error occurs.
RETURN: Query stops executing and error is
returned without need for statement rollback.
SAVEPOINT: DAM savepoints are used to roll
back the statement if an error occurs.
PARTIAL_UPD: [NonStop SQL/MP style]
Partial results are updated and an error is
returned.
text
self_referencing_update Indicates the method used to prevent the
halloween problem. Possible values are
dp2_locks, forced_sort, and
none_required.
text
TP663851.fm Page 60 Thursday, August 2, 2012 4:47 PM