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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-206
Publish/Subscribe Examples of SELECT
For more information, see ordered streams in SQL/MX Queuing and
Publish/Subscribe Services.
•
These examples show join operations with a base table and a stream:
SELECT *
FROM tab1, (SELECT * FROM STREAM(tab2)) AS tab2
WHERE tab1.a = tab2.a;
SELECT * FROM STREAM(tab1), tab2
WHERE tab1.a = tab2.a;
The two preceding queries yield identical results.
•
This example shows union operations with streams:
SELECT * FROM STREAM(tab1)
UNION ALL
SELECT * FROM STREAM(tab2);
•
These examples show the embedded delete statement as a table reference:
SELECT * FROM (DELETE FROM tab1) AS tab1;
SELECT * FROM (DELETE FROM tab1) AS tab1
ORDER BY b;
SELECT * FROM (DELETE FROM tab1) AS tab1
WHERE b = 1;
SELECT * FROM (DELETE FROM tab1) AS tab1
WHERE c > 1 ORDER BY b;
•
This example shows a return list in an embedded delete:
SELECT * FROM (DELETE FROM tab1 WHERE a > 1
RETURN OLD.*) AS tab1;
•
This example shows a return list in an embedded update:
SELECT * FROM
(UPDATE tab1 SET a = a + 1
WHERE a > 1 RETURN OLD.a, NEW.a)
AS tab1(old_a, new_a);
•
This example shows the SKIP CONFLICT access:
SELECT * FROM tab1 FOR SKIP CONFLICT ACCESS;
SELECT * FROM tab1, tab2
WHERE tab1.a = tab2.a
FOR SKIP CONFLICT ACCESS;