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-30
Predicates
Row-Value Constructors
Both NonStop SQL/MP and NonStop SQL/MX support the use of row-value
constructors within predicates. However, the 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, which allows you to specify
a TERMINATE character to indicate the end of the pattern within the pattern string. In
NonStop SQL/MP, you can use this clause when the column value and the comparison
value are of different lengths.
NonStop SQL/MX rewrites SQL/MP constraint and view text that uses the LIKE
predicate and TERMINATE character:
When converting an SQL/MP application to an SQL/MX application, replace the
TERMINATE character with the SQL/MX TRIM function, which can be used for the
same purpose as TERMINATE.
Original SQL/MP
predicate
expr1 LIKE expr2 TERMINATE trm
expr1 LIKE expr2 ESCAPE esc TERMINATE trm
Predicate
rewritten by
NonStop SQL/MX
expr1 LIKE SUBSTRING(expr2 FROM 1 FOR
(POSITION(trm)IN expr2) - 1)
expr1 LIKE SUBSTRING(expr2 FROM 1 FOR
(POSITION(trm) IN expr2) - 1) ESCAPE esc