Print Thread
Beautifier does not correctly format MERGE statement
#62435 03/12/21 10:00 AM
Joined: Apr 2006
Posts: 77
F
Member
OP Offline
Member
F
Joined: Apr 2006
Posts: 77
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 Files
Ji0twoa1Xc.png (29.97 KB, 42 downloads)
Re: Beautifier does not correctly format MERGE statement
Frank Kintrup #62439 03/13/21 09:50 AM
Joined: Aug 1999
Posts: 22,206
Member
Offline
Member
Joined: Aug 1999
Posts: 22,206
We'll fix it.


Marco Kalter
Allround Automations
Re: Beautifier does not correctly format MERGE statement
Frank Kintrup #62498 04/13/21 01:01 PM
Joined: Nov 2011
Posts: 23
Germany
Y
Member
Offline
Member
Y
Joined: Nov 2011
Posts: 23
Germany
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 ...


Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.080s Queries: 16 (0.047s) Memory: 2.5058 MB (Peak: 3.0380 MB) Data Comp: Off Server Time: 2024-04-29 15:12:14 UTC
Valid HTML 5 and Valid CSS