SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-23
SELECT Statements
In general, in NonStop SQL/MX, each row to be inserted is specified as a list of value
expressions or a row subquery. A value in a row can also be a scalar subquery. For
example, this SQL/MX statement inserts two rows into table JOB:
INSERT INTO JOB VALUES (1000, 'MANAGER'), ((SELECT *
FROM JOB1 WHERE JOBCODE = 2000));
In NonStop SQL/MP you usually perform multiple row inserts by setting a single row
insert to run in a loop. Even though NonStop SQL/MP is inserting a single row,
because it is looped, it will do this insert as a multiple row insert in DP2 using virtual
sequential block buffering (VSBB).
The NonStop SQL/MX architecture handles this differently, and it is more efficient to
use rowsets to perform multiple row inserts. If VSBB is on, EXPLAIN output will show
INSERT_VSBB as the operator.
SELECT Statements
Table Reference Within FROM Clause
In NonStop SQL/MP, a table reference within a FROM clause of a SELECT statement
can be a table, view, or joined table. The maximum number of tables that you can
specify in a FROM clause is 16. This maximum includes the underlying base tables of
views.
In NonStop SQL/MX, a table reference can also be a query expression or, stated more
precisely, the derived table determined by the evaluation of a query expression. A
query expression can be the UNION or JOIN of two (or more) table references. A
query expression can also be a simple table—a table value constructor generated by a
VALUES clause, TABLE clause, or a subquery.
In NonStop SQL/MX, an item in the select list can be a subquery:
SELECT (SELECT a FROM s WHERE b=t.b) FROM t;
In NonStop SQL/MX, you can use a joined table as a table reference in the FROM
clause of a SELECT statement. You specify an SQL:1999 joined table by connecting
the names of the two tables with the keywords NATURAL, CROSS, INNER, LEFT, or
RIGHT preceding the JOIN keyword. NonStop SQL/MP supports LEFT and INNER
joins only.
For example, the clause PARTS NATURAL JOIN ODETAIL (as a table reference in a
FROM clause) joins rows only where the values of all columns that have the same
name in both tables match. The clause PARTS NATURAL LEFT JOIN ODETAIL joins
rows where the values of all columns that have the same name in both tables match
plus rows from PARTS (the table to the left of the JOIN keyword) that do not meet this
condition.