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;