MP Users

DML Features

HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003

3-11

Aggregate Functions

In a few cases, NonStop SQL/MP allows a subquery within an aggregate in a

predicate. NonStop SQL/MX follows the ANSI standard and does not allow any

subqueries in an aggregate.

In order to comply with ANSI standards, NonStop SQL/MX does not move aggregate

predicates from the WHERE clause to a HAVING clause and does not move non-

aggregate predicates from the HAVING clause to the WHERE clause, as NonStop

SQL/MP does.

Predicates

Both NonStop SQL/MP and NonStop SQL/MX support the use of row-value

constructors within predicates. However, the NonStop SQL/MP implementation is not

SQL:1999 compliant. In NonStop SQL/MP, you are not required to enclose the

sequence of expressions of a row-value constructor in parentheses. In NonStop

SQL/MX, it is helpful (although not required) to do so. If you do not enclose the

sequence of expressions in parentheses, your resulting queries might be ambiguous.

For example, in this predicate, consisting of a comparison predicate and a NULL

predicate, SQL/MP row-value constructors use no parentheses:

WHERE a,b > 10,c AND p,q,r IS NULL

In contrast, in NonStop SQL/MX, the row-value constructors in this predicate use

parentheses:

WHERE (a,b) > (10,c) AND (p,q,r) IS NULL

Use row-value constructors in comparison predicates, the IN predicate, the NULL

predicate, and the quantified comparison predicates (ALL, ANY, SOME).

LIKE Predicate

Both NonStop SQL/MP and NonStop SQL/MX support the LIKE predicate. However,

NonStop SQL/MP has an extension to the ANSI standard. It allows you to specify a

TERMINATE character to indicate the end of the pattern within the pattern string. You

can use this clause when the column value and the comparison value are of different

lengths. NonStop SQL/MX supports the SUBSTRING and POSITION functions, which

can be used for the same purpose as TERMINATE.

NonStop SQL/MX rewrites SQL/MP constraint and view text that uses the LIKE

predicate and TERMINATE character:

Original SQL/MP predicate

column1 LIKE ?expr2 TERMINATE ?trm

column1 LIKE ?expr2 ESCAPE ?esc TERMINATE ?trm