SQL/MX Comparison Guide for SQL/MP Users

HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-1
3 DML Features
NonStop SQL/MX contains many SQL/MP extensions to the ANSI standard to enable
users to make a smooth transition to NonStop SQL/MX. See “Using NonStop SQL/MX
to Access SQL/MP Databases” in the SQL/MX Reference Manual if you have
questions about how to use SQL/MP tables with the SQL/MX compiler and executor.
Also, see the specific information you need to use date-time data types and literals,
INTERVAL data types and literals, and CHARACTER string data types and literals.
Although the current release of NonStop SQL/MP conforms to the SQL-89 standard,
some major differences exist between NonStop SQL/MP and SQL:1999 in the areas of
data types, expressions and functions, DML statement syntax, access options, and
transaction management. These areas also show up as differences between NonStop
SQL/MP and NonStop SQL/MX that are discussed in this section.
Data Types
NonStop SQL/MX supports most SQL/MP data types. Some SQL/MP DATETIME data
types are mapped to the SQL/MX SQL:1999 date-time data types DATE, TIME, and
TIMESTAMP. NonStop SQL/MX recognizes the nonstandard FRACTION keyword and
maps it directly to the fractional precision of the corresponding SQL:1999 compliant
data type.
Date-time Data Types
Both NonStop SQL/MP and NonStop SQL/MX support the SQL:1999 date-time data
types of DATE, TIME, and TIMESTAMP. Both NonStop SQL/MP and NonStop SQL/MX
support a number of nonstandard date-time types with specific start-end fields.
However, NonStop SQL/MX maps some date-time data types to DATE, TIME and
TIMESTAMP data types. NonStop SQL/MX does not support nonstandard date-time
data types with a start field of FRACTION.
Table 3-1
lists the mapping between date-time data types used in NonStop SQL/MP
and data types supported in NonStop SQL/MX. For information about how NonStop
SQL/MX processes nonstandard date-time data types, see the SQL/MX Reference
Manual.
Selecting Supported DATETIME Columns
NonStop SQL/MX supports accessing any SQL/MP DATETIME column except those
consisting of FRACTION only.
Suppose that an SQL/MP table has a DATETIME column defined as:
MPDateTimeCol DATETIME MONTH TO DAY
DEFAULT DATETIME '03-12' MONTH TO DAY
In NonStop SQL/MX you can select from this SQL/MP column as follows: