SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—523725-004
9-49
DIFF1 Function
DIFF1 Function
Considerations for DIFF1
Examples of DIFF1
The DIFF1 function is a sequence function that calculates the amount of change in an
expression from row to row in an intermediate result table ordered by a SEQUENCE
BY clause in a SELECT statement. See SEQUENCE BY Clause on page 7-18.
DIFF1 is an SQL/MX extension.
column-expression-a
specifies a derived column determined by the evaluation of the column expression.
If you specify only one column as an argument, DIFF1 returns the difference
between the value of the column in the current row and its value in the previous
row; this version calculates the unit change in the value from row to row.
column-expression-b
specifies a derived column determined by the evaluation of the column expression.
If you specify two columns as arguments, DIFF1 returns the difference in
consecutive values in column-expression-a divided by the difference in
consecutive values in column-expression-b.
The purpose of the second argument is to distribute the amount of change from
row to row evenly over some unit of change (usually time) in another column.
Considerations for DIFF1
Equivalent Result
If you specify one argument, the result of DIFF1 is equivalent to:
column-expression-a - OFFSET(column-expression-a, 1)
If you specify two arguments, the result of DIFF1 is equivalent to:
DIFF1(column-expression-a) / DIFF1(column-expression-b)
The two-argument version involves division by the result of the DIFF1 function. To
avoid divide-by-zero errors, make sure that column-expression-b does not
contain any duplicate values whose DIFF1 computation could result in a divisor of
zero.
Datetime Arguments
In general, NonStop SQL/MX does not allow division by a value of INTERVAL data
type. However, to permit use of the two-argument version of DIFF1 with times and
DIFF1 (column-expression-a [,column-expression-b])