SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-3
Selecting FRACTION-Only Interval Columns
This table lists the mapping between the interval data types used in NonStop SQL/MP
and those used in NonStop SQL/MX:
NonStop SQL/MX handles the interval data types in the next list in the same manner
as NonStop SQL/MP:
•
YEAR TO (YEAR, MONTH)
•
MONTH TO MONTH
•
DAY TO (DAY, HOUR, MINUTE)
•
HOUR TO (HOUR, MINUTE)
•
MINUTE TO MINUTE
For precision and default of interval data types, NonStop SQL/MP and NonStop
SQL/MX are the same. For additional information about interval data types, see the
SQL/MX Reference Manual.
Selecting FRACTION-Only Interval Columns
If you attempt to select data from a FRACTION-only interval column, the value is
returned as a string of '#' characters with the same display length as the length of the
column.
Suppose that an SQL/MP table has an INTERVAL column defined as:
MPIntervalCol INTERVAL FRACTION(6)
DEFAULT INTERVAL '123456' FRACTION(6)
You cannot select the data from this column. For example, if you enter this statement:
SELECT MPIntervalCol FROM MPTable;
MPIntervalCol
-------------
######
######
...
NonStop SQL/MP Interval Type Mapping in NonStop SQL/MX
INTERVAL DAY(n) TO SECOND INTERVAL DAY(n) TO SECOND(0)
INTERVAL DAY(n) TO FRACTION(f) INTERVAL DAY(n) TO SECOND(f)
INTERVAL HOUR(n) TO SECOND INTERVAL HOUR(n) TO SECOND(0)
INTERVAL HOUR(n) TO FRACTION(f) INTERVAL HOUR(n) TO SECOND(f)
INTERVAL MINUTE(n) TO SECOND INTERVAL MINUTE(n) TO SECOND(0)
INTERVAL MINUTE(n) TO FRACTION(f) INTERVAL MINUTE(n) TO SECOND(f)
INTERVAL SECOND(n) TO SECOND INTERVAL SECOND(n,0)
INTERVAL SECOND(n) TO FRACTION(f) INTERVAL SECOND(n,f)
INTERVAL FRACTION(n) TO FRACTION(f) Not supported