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 ...