By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

"Merge Into ..." Trouble

P: n/a
I'm going crazy trying to write a working merge-statement. Why doesn't
the following work:

MERGE INTO utSystem.t_Filter_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN NOT MATCHED AND FIl.MemberID IS NULL THEN
DELETE

WHEN NOT MATCHED AND FIl.MemberID IS NOT NULL THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

ELSE IGNORE
Error returned is: SQL0104N Auf "emberID IS NULL THEN" folgte das
unerwartete Token "DELETE". Zu den möglichen Token gehören:
"<merge_when_spec_body2>". SQLSTATE=42601. Sorry for German message.
It says that the token is not allowed in the context.

Also if I try replacing "AND Fil.MemberID IS NOT NULL" by an expression
using Res.MemberID it also says that its not allowed in the context.

Is the problem that if I filter by "NOT MATCHED" then there is no
cursor on a column to delte?

Regards
Janick

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Janick Bernet wrote:
I'm going crazy trying to write a working merge-statement. Why doesn't
the following work:

MERGE INTO utSystem.t_Filter_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN NOT MATCHED AND FIl.MemberID IS NULL THEN
DELETE

WHEN NOT MATCHED AND FIl.MemberID IS NOT NULL THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

ELSE IGNORE
Error returned is: SQL0104N Auf "emberID IS NULL THEN" folgte das
unerwartete Token "DELETE". Zu den möglichen Token gehören:
"<merge_when_spec_body2>". SQLSTATE=42601. Sorry for German message.
It says that the token is not allowed in the context.


I think you have a problem with the DELETE. The documentation says:

"Rows in the target that match the source can be deleted or updated as
specified, and rows that do not exist in the target can be inserted."

You have the case "NOT MATCHED", so you can only use INSERT as the
<modification-operation>, I'd say. After all, what is DB2 supposed to
delete if there is nothing that matched?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #2

P: n/a
Knut Stolze wrote:
Janick Bernet wrote:

I'm going crazy trying to write a working merge-statement. Why doesn't
the following work:

MERGE INTO utSystem.t_Filter_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN NOT MATCHED AND FIl.MemberID IS NULL THEN
DELETE

WHEN NOT MATCHED AND FIl.MemberID IS NOT NULL THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

ELSE IGNORE
Error returned is: SQL0104N Auf "emberID IS NULL THEN" folgte das
unerwartete Token "DELETE". Zu den möglichen Token gehören:
"<merge_when_spec_body2>". SQLSTATE=42601. Sorry for German message.
It says that the token is not allowed in the context.

I think you have a problem with the DELETE. The documentation says:

"Rows in the target that match the source can be deleted or updated as
specified, and rows that do not exist in the target can be inserted."

You have the case "NOT MATCHED", so you can only use INSERT as the
<modification-operation>, I'd say. After all, what is DB2 supposed to
delete if there is nothing that matched?

Correct, try this:
MERGE INTO utSystem.t_Filter_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
Knut, Serge thanks for you answers. If Knut is right, merge will not be
able to do what I want.

The Idea is to only insert rows which are in the table "Fil", to delete
rows which are not in the table "Fil" and to leave rows which are in
both tables untouched. So Serges solution does work, but doesn't do
what I want to :) Can I do with merge what I intended or is it not
suited for this? Is there another solution, better than delete all old
rows and then insert the new ones?

Regards
Janick

Nov 12 '05 #4

P: n/a
In my understanding, you want to do........
From these two sample data

------------------------- Commands Entered -------------------------
SELECT * FROM utSystem.t_Filter_Results
ORDER BY 1;
--------------------------------------------------------------------

CLUBID FILTERID MEMBERID
----------- ----------- -----------
100 50 1
200 50 2
300 50 3
400 40 3

4 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT * FROM club.vc_9;
--------------------------------------------------------------------

ID LOCATION# ENTERING_DATE LEAVING_DATE
----------- --------- ------------- ------------
1 Z?rich 2005-10-19 -
2 ZXrich 2005-10-19 -
3 Z?rich 2005-10-14 -
4 Z?rich 2005-10-29 2005-11-03

4 record(s) selected.

Get following result
------------------------- Commands Entered -------------------------
SELECT * FROM utSystem.t_Filter_Results;
--------------------------------------------------------------------

CLUBID FILTERID MEMBERID
----------- ----------- -----------
300 50 3
9 50 4
100 50 1

3 record(s) selected.

If so, one idea is DELETE and INSERT separately:
DELETE FROM utSystem.t_Filter_Results Res
WHERE NOT EXISTS
(SELECT *
FROM club.vc_9 Vc9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
AND Res.MemberID = Vc9.ID
AND Res.FilterID = 50
);

INSERT INTO utSystem.t_Filter_Results
WITH Fil AS (
SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9 Vc9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS NULL))
)
SELECT Fil.ClubID, Fil.FilterID, Fil.MemberID
FROM Fil
WHERE NOT EXISTS
(SELECT *
FROM utSystem.t_Filter_Results Res
WHERE Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID
);

My another idea using MERGE is something complicated and I suspect
performance. But, I'll show you it for your reference:
MERGE INTO utSystem.t_Filter_Results AS Res
USING (SELECT 9 as clubid, COALESCE(R.FilterID, 50) as filterid
, COALESCE(R.MemberID, V.ID) as MemberID
, CASE
WHEN V.ID IS NULL THEN
2
WHEN R.MemberID IS NULL
AND R.FilterID IS NULL THEN
1
ELSE 0
END AS Matched
FROM (SELECT *
FROM club.vc_9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE
IS NULL))
) AS V
FULL OUTER JOIN
utSystem.t_Filter_Results AS R
ON R.MemberID = V.ID
AND R.FilterID = 50
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID
AND 2 = Fil.Matched
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED AND 1 = Fil.Matched THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)
;

Nov 12 '05 #5

P: n/a
Tonkuma, thanks a lot for your detailed answer. I think I will go with
the first approach, which should be the faster and more readable one :)

Regards
Janick

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.