1

I have a set of SQL statements using distributed option of Invantive SQL that extract shipped goods information from Exact Online and create for each serial number shipped a ticket in Freshdesk, together with the consumer as a contact.

This works fine when connected to Exact Online and Freshdesk under one log on code. However, the end user uses a different log on code. In that case the set of SQL statements retrieves data from their test division in Exact Online instead of the correct production division.

When using no distributed option, I can change the division using:

use 123123

Where 123123 is the unique division number in the Exact Online country.

When connected both to Exact Online and Freshdesk, I get a:

itgenuse002: List of partitions could not be determined.

How can I enforce that the set of SQL statements is executed for a specific Exact Online division instead of the default one set at that moment for the log on code?

Sample SQL query that shows the problem:

create or replace table fulladdress@inmemorystorage --STAP 1.
as 
select acad.id
,      acad.name
,      acad.phone
,      acad.email
,      acad.addressline1 || '   ' || acad.postcode || '   ' || acad.city fulladdress
from   ExactonlineREST..Accounts@eolnl acad 
where  acad.status = 'C'
H Jansen
  • 319
  • 1
  • 8

2 Answers2

2

The use statement shown is for databases with exactly one data container. In that case, there is only one data container that can handle the question and everything runs smooth.

With a distributed query in Invantive SQL, you need to direct the use statement which data container to use. Otherwise, the first data container will try to handle it (in this case probably Freshdesk which has no concept of partitioning). That is similar to appending the data container alias to each tables as in:

select ...
from   table@eolnl
join   table2@freshdesk
on     ...

Here eolnl and freshdesk specify where the tables should looked up.

So, in this case use:

use 123123@eolnl

The same also holds for the set statement.

Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
0

From your code it seems you have multiple data containers running in your connection. From the user interface you can only set the partitions on the default data container.

However, there is an easy code solution to use. You have to know the alias of the data container you want to set the partition for. The use that alias in a use call (in this sample 123123 is the partition you want to choose):

use 123123@eolnl

Or to use all partitions available:

use all@eolnl
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325