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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-64
Examples of HashPartFunc
b as the partitioning key) and populate it with the 1000 data rows that currently
reside in cat.sch.table1:
>>SELECT partitionNum, count(*)
+>FROM (SELECT HashPartFunc(CAST(a AS INT NOT NULL),
CAST(b AS CHAR(3) NOT NULL) FOR 4)
+> FROM cat.sch.table1) AS Tmp(partitionNum)
+>GROUP BY partitionNum
+>ORDER BY partitionNum;
PARTITIONNUM (EXPR)
------------ --------------------
0 264
1 265
2 230
3 241
--- 4 row(s) selected.
•
The HashPartFunc function supports null values. For example:
>>SELECT HashPartFunc(cast(null as int) for 4) from
(values(0)) T;
(EXPR)
------
3
--- 1 row(s) selected.
Normally, it is important to cast the values to the desired type, but in the case of
null values, the type does not matter. Every null value hashes to the same value.
However, there is no harm in keeping the cast for consistency. For example:
>>SELECT HashPartFunc(cast(null as char(10)) for 4) from
(values(0)) T;
(EXPR)
------
3
--- 1 row(s) selected.
>>SELECT HashPartFunc(null for 4) from (values(0)) T;
(EXPR)
------
3
--- 1 row(s) selected.