Search code examples
sql-serverexact-onlinedistributed-database

itgendid012: Last part of the SQL statement has not been recognized on distributed Exact Online query

When I connect to a distributed database containing one Exact Online country (alias 'eol') and SQL Server (alias 'sqlserver'), I get an error when executing the following query to get the RGS balances from the current company over 2017:

select * 
from   me@eol me
join   exactonlinerest..GLSchemes@eol sme 
join   ReportingBalanceByClassification@eol(me.currentdivision, sme.id, 2017)

The original query on a non-distributed Invantive database was:

select * 
from   me
join   exactonlinerest..GLSchemes sme 
join   ReportingBalanceByClassification(me.currentdivision, sme.id, 2017)

and that one worked fine.

What is wrong in my query?

Solution

  • The correct syntax is:

    select * 
from   me@eol me
join   exactonlinerest..GLSchemes@eol sme 
join   ReportingBalanceByClassification(me.currentdivision, sme.id, 2017)@eol

    Place the alias of the data container (such as 'eol' here for Exact Online) after the parameters of a table function.