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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-68

LASTNOTNULL Function

The LASTNOTNULL function is a sequence function that returns the last nonnull value

of a column in an intermediate result table ordered by a SEQUENCE BY clause in a

SELECT statement. See SEQUENCE BY Clause on page 7-18.

LASTNOTNULL is an SQL/MX extension.

column-expression

specifies a derived column determined by the evaluation of the column expression.

If only null values have been returned, LASTNOTNULL returns null.

Examples of LASTNOTNULL

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 sequenced by TS:

•

Return the last nonnull value of a column:

SELECT LASTNOTNULL (I1) AS LASTNOTNULL

FROM mining.seqfcn SEQUENCE BY TS;

LASTNOTNULL

-----------

6215

19058

11966

--- 5 row(s) selected.

LASTNOTNULL (column-expression)

I1 TS

6215 TIMESTAMP '1950-03-05 08:32:09'

null TIMESTAMP '1951-02-15 14:35:49'

19058 TIMESTAMP '1955-05-18 08:40:10'

null TIMESTAMP '1960-09-19 14:40:39'

11966 TIMESTAMP '1964-05-01 16:41:02'