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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-150

THIS Function

The THIS function is a sequence function that is used in the ROWS SINCE function to

distinguish between the value of the column in the current row and the value of the

column in previous rows (in an intermediate result table ordered by a SEQUENCE BY

clause in a SELECT statement). See ROWS SINCE Function on page 9-120.

THIS is an SQL/MX extension.

column-expression

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

If the value of the expression is null, THIS returns null.

Considerations for THIS

Counting the Rows

You can use the THIS function only within the ROWS SINCE function. For each row,

the ROWS SINCE condition is evaluated in two steps:

1. The expression for THIS is evaluated for the current row. This value becomes a

constant.

2. The condition is evaluated for the result table, using a combination of the THIS

constant and the data for each row in the result table, starting with the previous row

as row 1 (up to the maximum number of rows or the size of the result table).

If a row is reached where the condition is true, ROWS SINCE returns the number of

rows counted so far. Otherwise, if the condition is never true within the result table

being considered, ROWS SINCE returns null. NonStop SQL/MX then goes to the next

row as the new current row and the THIS constant is reevaluated.

Example of THIS

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;

THIS (column-expression)