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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-48

DIFF2 Function

Considerations for DIFF2

Examples of DIFF2

The DIFF2 function is a sequence function that calculates the amount of change in a

DIFF1 value 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.

DIFF2 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, DIFF2 returns the difference

between the value of DIFF1(column-expression-a) in the current row and the

same result in the previous row.

column-expression-b

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

If you specify two columns as arguments, DIFF2 returns the difference in

consecutive values of DIFF1(column-expression-a) divided by the difference

in consecutive values in column-expression-b.

See DIFF1 Function on page 9-45.

Considerations for DIFF2

Equivalent Result

If you specify one argument, the result of DIFF2 is equivalent to:

DIFF1(column-expression-a)- OFFSET(DIFF1(column-expression-a),1)

If you specify two arguments, the result of DIFF2 is equivalent to:

DIFF2(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 DIFF2 with times and

DIFF2 (column-expression-a [,column-expression-b])