-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
ParseException when MERGE is used in a statement #1862
-
Hello everyone,
I'm trying to parse a statement to get the target and source tables and save them into a file.
I already did it successfully with INSERT and UPDATE, also when there are joins, unions, subqueries, and WITHs.
Unfortunately, I'm having issues with the MERGE clause.
For example, I have the following MERGE statement:
WITH
WMachine AS
( SELECT
DISTINCT
ProjCode,
PlantCode,
BuildingCode,
FloorCode,
Room
FROM
TAB_MachineLocation
WHERE
TRIM(Room) <> '' AND TRIM(Room) <> '-'
)
MERGE
TAB_RoomLocation AS TRoom
USING
WMachine
ON
(
TRoom.ProjCode = WMachine.ProjCode
AND TRoom.PlantCode = WMachine.PlantCode
AND TRoom.BuildingCode = WMachine.BuildingCode
AND TRoom.FloorCode = WMachine.FloorCode
AND TRoom.Room = WMachine.Room)
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
ProjCode,
PlantCode,
BuildingCode,
FloorCode,
Room
)
VALUES
(
WMachine.ProjCode,
WMachine.PlantCode,
WMachine.BuildingCode,
WMachine.FloorCode,
WMachine.Room
)
OUTPUT GETDATE() AS TimeAction,
$action as Action,
INSERTED.ProjCode,
INSERTED.PlantCode,
INSERTED.BuildingCode,
INSERTED.FloorCode,
INSERTED.Room
INTO
TAB_MergeActions_RoomLocation
It works fine on SQL Server but when I try to parse it with jsqlparser with the function CCJSqlParserUtil.parse(statement) it throws a net.sf.jsqlparser.parser.ParseException, that says:
Encountered unexpected token: "TAB_RoomLocation" <S_IDENTIFIER>
at line 16, column 9.
Was expecting:
"INTO"
Why does jsqlparser expect an INTO after the MERGE keyword? My SQL code works fine on SQL Server.
Thank you in advance for your help.
Beta Was this translation helpful? Give feedback.
All reactions
Greetings!
I have added support for WITH ... and the Output Clause.
You can test your statement online here.
WITH wmachine AS ( SELECT DISTINCT projcode , plantcode , buildingcode , floorcode , room FROM tab_machinelocation WHERE Trim( Room ) <> '' AND Trim( Room ) <> '-' ) MERGE INTO tab_roomlocation AS troom USING wmachine ON ( troom.projcode = wmachine.projcode AND troom.plantcode = wmachine.plantcode AND troom.buildingcode = wmachine.buildingcode AND troom.floorcode = wmachine.floorcode AND troom.room = wmachine.room ...
Replies: 4 comments 2 replies
-
Greetings,
your syntax is not SQL:2016 compliant.
Please see the supported synatx for the Merge Statement here: http://217.160.215.75:8080/jsqlformatter/JSQLParser/syntax_snapshot.html#merge
Beta Was this translation helpful? Give feedback.
All reactions
-
You can also test your statement online here.
Beta Was this translation helpful? Give feedback.
All reactions
-
You SQL:2016 compliant Merge would look like this:
MERGE INTO tab_roomlocation AS troom USING wmachine ON ( troom.projcode = wmachine.projcode AND troom.plantcode = wmachine.plantcode AND troom.buildingcode = wmachine.buildingcode AND troom.floorcode = wmachine.floorcode AND troom.room = wmachine.room ) WHEN NOT MATCHED THEN INSERT ( projcode , plantcode , buildingcode , floorcode , room ) VALUES ( wmachine.projcode , wmachine.plantcode , wmachine.buildingcode , wmachine.floorcode , wmachine.room ) ;
We do not support the Output clause for the MergeInsert.
Beta Was this translation helpful? Give feedback.
All reactions
-
Hello,
Thank you for your answers and the code example.
I tested the code with the link you sent me and it shows me an error, as you said.
It's strange that it's unsupported because I ran this code biweekly in a stored procedure on SQL Server 2019, and it never showed me any error and it works as expected.
Maybe it's a syntax supported only in SQL Server 2019 and not in 2016?
Anyway thank you for specifying that the OUTPUT clause is not supported, it's a pity but I will keep that in mind.
I will configure my program to accept the Merge statements with the syntax you showed me.
Have a nice day.
Beta Was this translation helpful? Give feedback.
All reactions
-
Greetings!
I have added support for WITH ... and the Output Clause.
You can test your statement online here.
WITH wmachine AS ( SELECT DISTINCT projcode , plantcode , buildingcode , floorcode , room FROM tab_machinelocation WHERE Trim( Room ) <> '' AND Trim( Room ) <> '-' ) MERGE INTO tab_roomlocation AS troom USING wmachine ON ( troom.projcode = wmachine.projcode AND troom.plantcode = wmachine.plantcode AND troom.buildingcode = wmachine.buildingcode AND troom.floorcode = wmachine.floorcode AND troom.room = wmachine.room ) WHEN NOT MATCHED /* BY TARGET */ THEN INSERT ( projcode , plantcode , buildingcode , floorcode , room ) VALUES ( wmachine.projcode , wmachine.plantcode , wmachine.buildingcode , wmachine.floorcode , wmachine.room ) OUTPUT Getdate() AS timeaction , $action AS action , inserted.projcode , inserted.plantcode , inserted.buildingcode , inserted.floorcode , inserted.room INTO tab_mergeactions_roomlocation ;
Beta Was this translation helpful? Give feedback.
All reactions
-
Wow, that's great! Thank you so much.
I will test it when I finish implementing the MERGE management in my program.
And I will change the MERGE syntax used in my store procedure to the one supported by you.
Thank you again and have a great day.
Beta Was this translation helpful? Give feedback.