I know there are differences in MERGE Statement in UDB and z/OS like in z/Os only one 'WHEN MATCHED', one 'WHEN NOT MATCHED' are allowed.
My question is more about USING clause, does it always have to be
( USING VALUES ...) FOR n ROWS or can we have a SELECT clause like in the example below:
MERGE INTO inventory AS in
USING (SELECT partno, description, count FROM shipment
WHERE shipment.partno IS NOT NULL) AS sh
ON (in.partno = sh.partno)
WHEN MATCHED THEN
UPDATE SET
description = sh.description,
quantity = in.quantity + sh.count
WHEN NOT MATCHED THEN
INSERT
(partno, description, quantity)
VALUES (sh.partno, sh.description, sh.count)
If SELECT is NOT allowed, the host-variable-array (which I believe will fill the VALUES ) is supported only in C/C++, COBOL and PL/1 (according to z/OS V9.1 SQL reference). I need to work in JAVA. What is my workaround, if any?
Appreciate your help