Please take a look at the formatting of this MERGE statement:
[Linked Image]
I see three issues her, two major, one minor:

  • (Major) The DELETE WHERE part of the WHEN MATCHED THEN UPDATE part is completely wrong!
    It must be inline with the UPDATE statement.
  • (Major) The WHERE clause of the WHEN NOT MATCHED THEN INSERT part must be inline with the INSERT statement.
  • (Minor) The field and value list of the Insert part could be one item per line.


Attached picture Ji0twoa1Xc.png
We'll fix it.
While working on the formatting of the MERGE statement.
I have an additional wish: I would like see (or have the option to control that) the multiple Join-Conditions inside the ON ( ...) Clause in separate lines.

For select-Statements, you can either include the join-conditions inside brackets. Then you get them on one line:
--join conditions inside brackets
SELECT *
FROM destTable dst
JOIN sourceTable src ON (dst.id1 = src.id1 AND dst.id2 = src.id2 AND dst.id3 = src.id3 AND src.genDate > SYSDATE);


If you don't put brackets around, you get them on separate lines:
--regular join on two columns
SELECT *
FROM destTable dst
JOIN sourceTable src ON dst.id1 = src.id1
AND dst.id2 = src.id2
AND dst.id3 = src.id3
AND src.genDate > SYSDATE;


For the MERGE statement, you don't have a choice, the brackets are part of the syntax, on thus you always get:

--merge: join condition are always inside brackets, but I would like to have here
MERGE INTO destTable dst
USING (SELECT id1
,id2
,id3
,val1
,val2
,genDate
FROM sourceTable) src
ON (dst.id1 = src.id1 AND dst.id2 = src.id2 AND dst.id3 = src.id3 AND src.genDate > SYSDATE)
WHEN MATCHED THEN
UPDATE
SET dst.val1 = src.val1
,dst.val2 = src.val2
WHERE DECODE(dst.val1, src.val1, 0, 1) = 1
OR DECODE(dst.val2, src.val2, 0, 1) = 1;


This is of course no problem with just one or two join conditions, but if you have more, it becomes unreadable.

MERGE INTO destTable dst
USING (SELECT id1
,id2
,id3
,val1
,val2
,genDate
FROM sourceTable) src
ON ( dst.id1 = src.id1
AND dst.id2 = src.id2
AND dst.id3 = src.id3
AND src.genDate > SYSDATE)
WHEN MATCHED THEN
UPDATE
SET ...
© Allround Automations forums