SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-46
Examples of DIFF1
dates, NonStop SQL/MX relaxes this restriction and allows division by a value of
INTERVAL data type.
Examples of DIFF1
Suppose that SEQFCN has been created as:
CREATE TABLE $db.mining.seqfcn
(I1 INTEGER,TS TIMESTAMP);
Within MXCI, the ANSI alias name has been mapped as:
CREATE SQLMP ALIAS db.mining.seqfcn $db.mining.seqfcn;
The table SEQFCN has columns I1 and TS with data that is sequenced by column TS:
•
Retrieve the difference between the I1 column in the current row and the I1 column
in the previous row:
SELECT DIFF1 (I1) AS DIFF1_I1
FROM mining.seqfcn
SEQUENCE BY TS;
DIFF1_I1
------------
?
21959
-9116
-14461
7369
--- 5 row(s) selected.
Note that the first row retrieved displays null because the offset from the current
row does not fall within the results set.
I1 TS
6215 TIMESTAMP '1950-03-05 08:32:09'
28174 TIMESTAMP '1951-02-15 14:35:49'
19058 TIMESTAMP '1955-05-18 08:40:10'
4597 TIMESTAMP '1960-09-19 14:40:39'
11966 TIMESTAMP '1964-05-01 16:41:02'