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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-49
Examples of DIFF2
dates, NonStop SQL/MX relaxes this restriction and allows division by a value of
INTERVAL data type.
Examples of DIFF2
Suppose that SEQFCN has been created as:
CREATE TABLE 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 the TS
column:
•
Retrieve the difference between the value of DIFF1(I1) in the current row and the
same result in the previous row:
SELECT DIFF2 (I1) AS DIFF2_I1
FROM mining.seqfcn
SEQUENCE BY TS;
DIFF2_I1
--------------------
?
?
-31075
-5345
21830
--- 5 row(s) selected.
Note that the results are equal to the difference of DIFF1(I1) for the current row
and DIFF1(I1) of the previous row. For example, in the third row of the output of
this example, -31075 is equal to -9116 minus 21959. The value -9116 is the result
of DIFF1(I1) for the current row, and the value 21959 is the result of DIFF1(I1) for
the previous row. See Examples of DIFF1
on page 9-46.
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'