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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-102

OFFSET Function

The OFFSET function is a sequence function that retrieves columns from previous

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

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

OFFSET is an SQL/MX extension.

column-expression

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

number-rows

is an SQL numeric value expression of signed data type SMALLINT or INTEGER

that specifies the offset as the number of rows from the current row. If the number

of rows exceeds max-rows, OFFSET returns OFFSET(column-

expression,max-rows). If the number of rows is out of range and max-rows is

not specified or is out of range, OFFSET returns null. The number of rows is out of

range if it is larger than the size of the result table, negative, or NULL.

max-rows

is an SQL numeric value expression of signed data type SMALLINT or INTEGER

that specifies the maximum number of rows of the offset.

Examples of OFFSET

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:

OFFSET (column-expression,number-rows [,max-rows])

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'