The restrictions for specifying deterministic and nondeterministic functions in the secondary conditions of an ON clause are as follows:
There are no restrictions regarding either deterministic or nondeterministic functions that are specified in the secondary conditions of a MERGE request ON clause.
For example, the following case is a valid MERGE request because the deterministic_udf and non_deterministic_udf functions are specified in the ON clause as secondary conditions, which is valid.
MERGE INTO t1
USING t2
ON a1=a2 AND b1=deterministic_udf(b2)
AND c1=non_deterministic_udf(c2)
WHEN MATCHED THEN
UPDATE SET b1=b2
WHEN NOT MATCHED THEN
INSERT (a2, deterministic_udf(b2),
non_deterministic_udf(c2));
The RANDOM function is nondeterministic by definition. Therefore, the restrictions that apply to nondeterministic UDFs apply equally to RANDOM.
The following MERGE request is valid because it specifies a RANDOM function in a secondary condition of its ON clause.
MERGE INTO t1
USING t2
ON a1=a2 AND b1=RANDOM(1,100)
WHEN MATCHED THEN
UPDATE SET b1=b2
WHEN NOT MATCHED THEN
INSERT (a2, RANDOM(1,100), c2);