×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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!
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 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.