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-24
UPDATE Statements
GROUP BY Clause and Numerical Arguments
In NonStop SQL/MP, the GROUP BY clause allows you to use numerical arguments,
for example:
SELECT a + b FROM t GROUP BY 1;
NonStop SQL/MX does not allow the use of numerical arguments with the GROUP BY
clause. However, you can use a derived query to achieve the same purpose as shown
next:
SELECT c FROM (SELECT a + b FROM t) x(c) GROUP BY c;
Placement of GROUP BY Clause
NonStop SQL/MX requires the GROUP BY clause, if one exists, to precede a HAVING
clause. In NonStop SQL/MP, you can place the clauses in any order.
UPDATE Statements
Source Value for a Column Referenced in the SET Clause
In NonStop SQL/MX, the source value for a column referenced in the SET clause of an
UPDATE statement is an SQL value expression. In particular, a value expression can
be a scalar subquery:
UPDATE JOB SET jobdesc=(SELECT jobdesc from JOB1
WHERE jobcode=2000)
WHERE jobcode=2000;
In NonStop SQL/MP, the expression cannot be a subquery.
Parallel Plans
In NonStop SQL/MP, you cannot have a parallel plan where the UPDATE statement
contains the WHERE CURRENT OF clause. In NonStop SQL/MX, you can have
parallel plans with the WHERE CURRENT OF clause.
Access Options and Isolation Levels
NonStop SQL/MP allows you to specify access options for each DML statement. In
NonStop SQL/MP, the
statement level access options are BROWSE, STABLE, and
REPEATABLE.
NonStop SQL/MX allows you to specify the isolation level at both the
statement and
transaction level. (Isolation level, SQL:1999 terminology, means the same as access
option.) In NonStop SQL/MX, the isolation levels for both statements and transactions
are READ UNCOMMITTED, READ COMMITTED, SERIALIZABLE (or REPEATABLE
READ), and STABLE. Isolation levels set at the statement level take precedence over
the transaction level settings. Isolation levels for statements are extensions to the ANSI