Re: SQL MERGE

Just a caution - it is not wise to use documentation for a different SQL RDBMS to see what we can do on the i. z is at least in the Db2 family, and IBM do try to make those align. You should have been able to find the IBM i documentation for MERGE. I recommend finding the Knowledge Center for IBM i for your version of it.

And googling on "merge sql ibm i" brought me to several hits right away for IBM i. Maybe Google knows what I usually do!  :)

Cheers
Vern

On 7/30/2023 12:21 AM, x y wrote:

In a data conversion exercise, I'm getting dirty data and that's causing
lots of duplicate key exceptions (which appear to have a big performance
hit). Using MERGE came up as a potential solution and I RTFM (meaning I
Googled).

Following an example in the z/OS DB2 manual, this works:
MERGE INTO arp001 USING (VALUES ('X1', 'X1 DESC'))
AS newdata (cmcust, cmname)
ON arp001.cmcust = newdata.cmcust
WHEN NOT MATCHED THEN *INSERT* (cmcust, cmname) VALUES
(newdata.cmcust, newdata.cmname)
WHEN MATCHED THEN UPDATE SET cmname = newdata.cmname;

But this doesn't. The change is in using program variables instead of
literals.
MERGE INTO arp001 USING (VALUES (:value1, :value2))
AS newdata (cmcust, cmname)
ON arp001.cmcust = newdata.cmcust
WHEN NOT MATCHED THEN *INSERT* (cmcust, cmname) VALUES
(newdata.cmcust, newdata.cmname)
WHEN MATCHED THEN UPDATE SET cmname = newdata.cmname;

Message: [SQL0584] NULL, UNKNOWN, or parameter marker in VALUES not
allowed. Cause . . . . . : Each column in a VALUES clause must have a
data type. All rows for a column cannot contain NULL, UNKNOWN, a parameter
marker, or the RAISE_ERROR scalar function. Recovery . . . : Ensure
every column has at least one value with a defined type. A CAST
specification can be used to assign a type. Try the request again.

Table ARP001 has more than two columns, all type 3 (not null with default).

ACS's "Run SQL Scripts" option provides this example:
MERGE INTO t1 USING
(SELECT id, c2 FROM t2) x ON
t1.id = x.id
WHEN NOT MATCHED THEN *INSERT* VALUES (id, c2)
WHEN MATCHED THEN UPDATE SET c2 = x.c2;

It appears that either program variables aren't allowed or I have to
specify every column name in WHEN NOT MATCHED.

Suggestions/solutions will be appreciated--thank you!