Skip to main content
  • EN
Hi,

 

Is it possible to run a SQL Merge Statement from within the SQL Executor?  My SQL DBA provided me with the following Merge Statement which I put in the Executor and I've also inserted the FME_SQL_Delimiter statement at the beginning because I was getting an error about semicolons being detected.

 

 

 

FME_SQL_DELMITER^ MERGE TERANET_OWNERSHIPPROPERTYREPORT AS Detail USING TXC_STAGING_OWNERSHIPPROPERTYREPORT AS Stage ON (Detail.PIN = Stage.PIN and Detail.lronumber = Stage.LRONumber and Detail.instrumentnumber = Stage.instrumentnumber and Detail.instrumenttype = Stage.instrumenttype) WHEN NOT MATCHED THEN INSERT (LRONumber, PIN, StreetName, StreetNumber, StreetSuffix, UnitTypeCode, UnitNumber, MunicipalityName, Description, OwnerName1, OwnerName2, OwnerName3, OwnerNameMore, RegistrationSystemCode, InstrumentNumber, InstrumentType, ConsiderationAmount, RegistrationDate, PartyTo1, PartyTo2, PartyTo3, PartyToMore, PartyFrom1, PartyFrom2, PartyFrom3, PartyFromMore) VALUES(Stage.lronumber, Stage.pin, Stage.streetname, Stage.streetnumber, Stage.streetsuffix, Stage.unittypecode, Stage.unitnumber, Stage.municipalityname, Stage.description, Stage.ownername1, Stage.ownername2, Stage.ownername3, Stage.ownernamemore, Stage.registrationsystemcode, Stage.instrumentnumber, Stage.instrumenttype, Stage.considerationamount, Stage.registrationdate, Stage.partyto1, Stage.partyto2, Stage.partyto3, Stage.partytomore, Stage.partyfrom1, Stage.partyfrom2, Stage.partyfrom3, Stage.partyfrommore) WHEN MATCHED AND(ISNULL(Detail.streetname,'') <> ISNULL(Stage.streetname,'') OR ISNULL(Detail.streetnumber,0) <> ISNULL(Stage.streetnumber,0) OR ISNULL(Detail.streetsuffix,'') <> ISNULL(Stage.streetsuffix,'') OR ISNULL(Detail.unittypecode,'') <> ISNULL(Stage.unittypecode,'') OR ISNULL(Detail.unitnumber,'') <> ISNULL(Stage.unitnumber,'') OR ISNULL(Detail.municipalityname,'') <> ISNULL(Stage.municipalityname,'') OR ISNULL(Detail.description,'') <> ISNULL(Stage.description,'') OR ISNULL(Detail.ownername1,'') <> ISNULL(Stage.ownername1,'') OR ISNULL(Detail.ownername2,'') <> ISNULL(Stage.ownername2,'') OR ISNULL(Detail.ownername3,'') <> ISNULL(Stage.ownername3,'') OR ISNULL(Detail.ownernamemore,'') <> ISNULL(Stage.ownernamemore,'') OR ISNULL(Detail.registrationsystemcode,'') <> ISNULL(Stage.registrationsystemcode,'') OR ISNULL(Detail.considerationamount,'') <> ISNULL(Stage.considerationamount,'') OR ISNULL(Detail.registrationdate,convert(datetime,'1900-01-01')) <> ISNULL(Stage.registrationdate,convert(datetime,'1900-01-01')) OR ISNULL(Detail.partyto1,'') <> ISNULL(Stage.partyto1,'') OR ISNULL(Detail.partyto2,'') <> ISNULL(Stage.partyto2,'') OR ISNULL(Detail.partyto3,'') <> ISNULL(Stage.partyto3,'') OR ISNULL(Detail.partytomore,'') <> ISNULL(Stage.partytomore,'') OR ISNULL(Detail.partyfrom1,'') <> ISNULL(Stage.partyfrom1,'') OR ISNULL(Detail.partyfrom2,'') <> ISNULL(Stage.partyfrom2,'') OR ISNULL(Detail.partyfrom3,'') <> ISNULL(Stage.partyfrom3,'') OR ISNULL(Detail.partyfrommore,'') <> ISNULL(Stage.partyfrommore,'') ) THEN UPDATE SET Detail.streetname = Stage.streetname, Detail.streetnumber = Stage.streetnumber, Detail.streetsuffix = Stage.streetsuffix, Detail.unittypecode = Stage.unittypecode, Detail.unitnumber = Stage.unitnumber, Detail.municipalityname = Stage.municipalityname, Detail.description = Stage.description, Detail.ownername1 = Stage.ownername1, Detail.ownername2 = Stage.ownername2, Detail.ownername3 = Stage.ownername3, Detail.ownernamemore = Stage.ownernamemore, Detail.registrationsystemcode = Stage.registrationsystemcode, Detail.considerationamount = Stage.considerationamount, Detail.registrationdate = Stage.registrationdate, Detail.partyto1 = Stage.partyto1, Detail.partyto2 = Stage.partyto2, Detail.partyto3 = Stage.partyto3, Detail.partytomore = Stage.partytomore, Detail.partyfrom1 = Stage.partyfrom1, Detail.partyfrom2 = Stage.partyfrom2, Detail.partyfrom3 = Stage.partyfrom3, Detail.partyfrommore = Stage.partyfrommore;^

 

 

Thanks,

 

Chris
Hi

 

 

Did you try? FME simply passes the SQL on to the database, so it ought to work (with some limitations).

 

 

If you got an error, could you poste the relevant section of the log here?

 

 

David
In addition, note that "FME_SQL_DELIMITER" and the delimiter character have to be separated with a white space.
Hi David,

 

Here is the error statement:

 

MS SQL Server (Spatial) Reader: Query failed, possibly due to a malformed statement.

 

 

Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: 'FME_SQL_DELMITER ^ MERGE TERANET_OWNERSHIPPROPERTYREPORT.....

 

= Stage.partyfrommore; ^ '.  Provider error `(-2147217900) Incorrect syntax near '^'.'
If you copied&pasted the message from the log directly, probably the primary reason for the error is a typo on FME_SQL_DELIMITER.

 

And it's not essential to declare a specific delimiter if you will execute just one SQL statement.
Just be aware that the log entry about the FME_SQL_DELIMITER is a warning, not an error. Sometimes the warning is incorrect, and you should just ignore it. For this particular case, I'd just skip defining it altogether.

 

 

David
agree, but a typo for the keyword will not be allowable anyway.

 

 

specify a delimiter. SQL is: 'FME_SQL_DELMITER ^ MERGE

 

 

can't find 'I' between L and M...

An another solution: Use the PythonCreator or PythonCaller to execute the SQL statement.

FME_SQL_DELIMITER needs an argument (what delimiter is used). But as it is just one SQL statement you do not need the delimiter. Just remove the semicolon at the end.

Reply


Community Stats

30,108
Posts
114,295
Replies
37,745
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.