|
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!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
