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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-121
Examples of ROWS SINCE
Examples of ROWS SINCE
Suppose that SEQFCN has been created as:
CREATE TABLE $db.mining.seqfcn
(I1 INTEGER,I2 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, I2, and TS with data that is sequenced by column
TS:
•
Return the number of rows since the condition I1 IS NULL became true:
SELECT ROWS SINCE (I1 IS NULL) AS ROWS_SINCE_NULL
FROM mining.seqfcn
SEQUENCE BY TS;
ROWS_SINCE_NULL
---------------
?
?
1
2
1
--- 5 row(s) selected.
•
Return the number of rows since the condition I1 < I2 became true:
SELECT ROWS SINCE (I1<I2), ROWS SINCE INCLUSIVE (I1<I2)
FROM mining.seqfcn
SEQUENCE BY TS;
(EXPR) (EXPR)
--------------- ---------------
? 0
1 1
2 0
1 1
2 0
--- 5 row(s) selected.
I1 I2 TS
6215 7516 TIMESTAMP '1950-03-05 08:32:09'
null 497 TIMESTAMP '1951-02-15 14:35:49'
19058 26165 TIMESTAMP '1955-05-18 08:40:10'
null 9681 TIMESTAMP '1960-09-19 14:40:39'
11966 12356 TIMESTAMP '1964-05-01 16:41:02'