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-29
Predicates
Aggregate Predicates in WHERE and HAVING Clauses
In NonStop SQL/MP, the WHERE clause can include predicates with aggregate
functions. NonStop SQL/MP moves aggregate predicates to the HAVING clause even
if the query does not include a HAVING clause. The HAVING clause can include
predicates without aggregate functions, which NonStop SQL/MP moves to the WHERE
clause.
Conforming to the SQL:1999 standard, NonStop SQL/MX disallows aggregate
functions in predicates in the WHERE clause, and any column in the HAVING clause
that is not an argument of an aggregate function must be a grouping column. The
HAVING clause must follow the GROUP BY clause because the results of the GROUP
BY clause flow to the HAVING clause.
For example, when converting the SQL/MP application to an SQL/MX application,
change this SQL/MP statement:
SELECT col1, COUNT(*)
FROM tab
GROUP BY col1
HAVING col2 < 25;
To this SQL/MX-compliant syntax:
SELECT col1, COUNT(*)
FROM tab
WHERE col2 < 25
GROUP BY col1;
LIKE Yes, but
changes
might be
required.
NonStop SQL/MX does not support the
TERMINATE character. For more information,
see the LIKE Predicate on page 10-30.
NonStop SQL/MP and NonStop SQL/MX differ
in how they handle SQL/MP NCHAR columns
in a LIKE predicate. For more information, see
Using NCHAR Host Variables and Literals
on
page 10-34.
NULL Yes, but
changes
might be
required.
For possible changes, see:
•
Aggregate Predicates in WHERE and
HAVING Clauses on page 10-29
•
Row-Value Constructors on page 10-30
Quantified Yes, but
changes
might be
required.
For possible changes, see:
•
Aggregate Predicates in WHERE and
HAVING Clauses on page 10-29
•
Row-Value Constructors on page 10-30
Table 10-8. SQL/MX and SQL/MP Predicates (page 2 of 2)
SQL/MP Predicates
Implemented
in SQL/MX? Comments