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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-120

ROWS SINCE Function

The ROWS SINCE function is a sequence function that returns the number of rows

counted since the specified condition was last true in the intermediate result table

ordered by a SEQUENCE BY clause in a SELECT statement. See SEQUENCE BY

Clause on page 7-18.

ROWS SINCE is an SQL/MX extension.

INCLUSIVE

specifies the current row is to be considered. If you specify INCLUSIVE, the

condition is evaluated in the current row. Otherwise, the condition is evaluated

beginning with the previous row. If you specify INCLUSIVE and the condition is

true in the current row, ROWS SINCE returns 0.

condition

specifies a condition to be considered for each row in the result table. Each column

in condition must be a column that exists in the result table. If the condition has

never been true for the result table, ROWS SINCE returns null.

max-rows

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

that specifies the maximum number of rows from the current row to consider. If the

condition has never been true for max-rows from the current row, or if max-rows

is negative or null, ROWS SINCE returns null.

Considerations for ROWS SINCE

Counting the Rows

If you specify INCLUSIVE, the condition in each row of the result table is evaluated

starting with the current row as row 0 (zero) (up to the maximum number of rows or the

size of the result table). Otherwise, the condition is evaluated starting with the previous

row as row 1.

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.

ROWS SINCE [INCLUSIVE] (condition [,max-rows])