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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-198
Considerations for UNION
Access Modes and the UNION Operator
In a query containing the UNION operator, if you specify an access option for the
second operand before the ORDER BY clause (or if the UNION has no ORDER BY
clause) and you do not specify an option for the first operand, the first operand inherits
the session’s transaction isolation level setting. If this setting is different from the one
you specified for the second operand, NonStop SQL/MX issues a warning. For
example:
SELECT common.isma_no FROM sdcommon common
WHERE common.sec_status='L'
UNION
SELECT main.isma_no FROM sdmain main
WHERE main.iss_eligible='Y'
FOR READ UNCOMMITTED ACCESS
ORDER BY 1 ASCENDING;
This statement will receive a warning:
*** WARNING[3192] Union operands sdcommon common and sdmain
main have different transaction access/lock modes.
If you want the access you specified for the second operand to apply to both SELECT
items in this type of query, use one of these strategies:
•
Specify the desired access mode for each SELECT:
SELECT common.isma_no FROM sdcommon common
WHERE common.sec_status='L'
FOR READ UNCOMMITTED ACCESS
UNION
SELECT main.isma_no FROM sdmain main
WHERE main.iss_eligible='Y'
FOR READ UNCOMMITTED ACCESS
ORDER BY 1 ASCENDING;
•
Use a table subquery to enclose the UNION, and apply the access mode to the
main query. This statement receives a warning because NonStop SQL/MX treats
the access mode on the second SELECT as applicable only to that second
SELECT:
SELECT a
from t046a where b=1
UNION
SELECT b from t046b where a=2
for browse access;
This statement uses a table subquery to apply the access mode to both queries:
SELECT c from
(SELECT a from t046a where b=1
UNION
SELECT b from t046b where a=2) as t(c)
for browse access;