How to get debtors from numbers for top 3 partitions

Question

I want to get debtors from numbers from TOP 3 partitions. I am using this query to select partitions:

use select top 1 code, data_container_alias from systempartitions@datadictionary where data_container_alias = 'nmbrs' 

And this query to get debtors:

select * 
from   Debtors@nmbrs de 
order
by     de.id

End result: I get a lot of Debtors (say > 1.000) but it doesn't depend on how many partitions I select.

What I am trying to achieve is to get a debtor companies list using this query

select de.PartitionID
,      de.ID
,      de.Number
,      de.Name
,      de.PhoneNumber
,      de.FaxNumber
,      de.Email
,      de.LoonaangifteTijdvak
,      de.KvkNr
,      d.ID as DebtorID
from   Debtors@nmbrs d 
full
outer
join   DebtorCompanies(d.ID)@nmbrs de 
order
by     de.PartitionID

But my end result is cartesian product of (all Debtors) X (all Partitions/Companies)

How can I get debtors for specific partitions and companies? Is there a reason why partitions are at a company and not a debtor level?

Answer 1

Partitions are often segmented per legal entity, so each company is a separate partition. In the case of Nmbrs, they do not really have a partitioned database like salesforce org or Exact Online company, but they have companies for which they do the payrolling.

Each company payrolled is part of a debtor, which is a somewhat unclear concept for me. It seems to be "debtor" from the view point of Nmbrs itself: the company they sent their invoice to.

In my test environment there is solely one debtor which has four companies with employees.

In the test environment, the following query returns four rows:

select dtr.* prefix with 'dtr_'
,      dcy.* prefix with 'dcy_'
from   debtors dtr
join   debtorcompanies(dtr.id) dcy

But I can not judge whether it is a cartesian product or not since the count(*) of debtors is just 1. I will check and update answer after getting another debtor into the test environment.