You must tell MERGE the data types of the fields contained within the VALUES clause.
MERGE INTO arp001 USING (VALUES (
Cast( :value1 as Decimal(15,0)),
Cast( :value2 as Varchar(100))
)) 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;
Here is an SQL statement I've written to generate an SQL Merge statement from system tables.
https://gist.github.com/chrishiebert/2c496d9ca09b2f4a3bc493cd6caa77c2
It may not work for all data types, but it gets you closer than writing everything from scratch.
I wrote the statement on V7R3.
--
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of x y
Sent: Saturday, July 29, 2023 11:21 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL MERGE
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!
midrange.com
