Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Sql Prompt unable to parse MERGE ON MATCH $from_id $to_id edge graph clause

AdrianFilipescuAdrianFilipescu Posts: 1 New member
edited December 15, 2020 3:06PM in SQL Prompt

I have the following Merge syntax for an edge table named "owns":

;WITH UidCTE
    AS ( SELECT DISTINCT P.[UId] AS PersonUid ,
                        S.[UId] AS SecurityUid
         FROM ........
      )
MERGE graph.owns AS TGT
USING graph.[User] AS SourceFrom
JOIN UidCTE CTE ON SourceFrom.Id = CTE.PersonUid
JOIN graph.[Security] AS SourceTo ON CTE.SecurityUid = SourceTo.Id
ON MATCH (SourceFrom-(TGT)->SourceTo)
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( $from_id , $to_id )
    VALUES ( SourceFrom.$node_id, SourceTo.$node_id);

Although the query runs correct in a compatibility 140 DB it crashes near $from_id , $to_id with incorrect syntax when trying to format the code using the latest SQL Prompt 10.5 version.
I'm using SSMS 15.
If I remove ( $from_id , $to_id ) and if I changed them with the "real" column names the apply layout refactoring will crash with "Object not set as an instance of an object" near the MATCH clause .

Answers

Sign In or Register to comment.