SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-202
Considerations for UNION
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;
•
Specify the access mode after the ORDER BY clause:
SELECT common.isma_no
from sdcommon common
where common.sec_status='L'
UNION
SELECT main.isma_no
from sdmain main