HASH_ROW

The HASH_ROW function returns the SHA512 hash value of the data content of a row.

table-designator

A table designator that can be used to qualify a column in the same relative location in the SQL statement as the HASH_ROW function. For more information about table designators, see Table designators.

In SQL naming, the table name may be qualified. In system naming, the table name cannot be qualified.

The table-designator must not identify a table-function, a collection-derived-table, a VALUES clause, a common table expression, or a nested table expression. If the argument identifies a view, its outer subselect must directly or indirectly reference a table.

If the argument identifies a view, the function returns the row hash value for its base table. If the argument identifies a view derived from more than one base table, the function returns the row hash value of the first table in the outer subselect of the view.

If the argument identifies a distributed table, the function returns the row hash value of the row on the node where the row is located.

The argument must not identify a view whose outer fullselect includes an aggregate function, a GROUP BY clause, a HAVING clause, a UNION, INTERSECT, or EXCEPT clause, a DISTINCT clause, a VALUES clause, or a table-function. The HASH_ROW function cannot be specified in a SELECT clause if the fullselect contains an aggregate function, a GROUP BY clause, or a HAVING clause

The data type of the result is a BINARY(64) value. The result can be null.

Example

Determine whether all the rows for the EMPLOYEE table in LIB1 exist and contain the same data values as the EMPLOYEE table in LIB2.

WITH LIB1_HASH AS (
    SELECT EMPNO, HASH_ROW(E1) ROW_VALUE, RID(E1) ROW_RID
         FROM LIB1.EMPLOYEE E1),
 LIB2_HASH AS (
    SELECT EMPNO, HASH_ROW(E2) ROW_VALUE, RID(E2) ROW_RID
         FROM LIB2.EMPLOYEE E2)
SELECT H1.EMPNO, H1.ROW_RID, H2.ROW_RID 
         FROM LIB1_HASH H1 FULL OUTER JOIN LIB2_HASH H2
               ON H1.EMPNO = H2.EMPNO
         WHERE H1.ROW_VALUE <> H2.ROW_VALUE;