Invantive Data Hub query on Exact Online returns too many rows

Question

when I use Invantive Data Hub to dowload data from multiple Exact Online companies , I get duplicate rows when I expect one row per company.

I use the following query:

select gla.code
,      gla.description
,      gla.division
,      glc.glclassification_code_attr
,      glc.glclassification_description
,      glc.division_code
from   ExactOnlineREST..GLAccounts gla
join   ExactOnlineXML..GLAccountClassifications glc
on     gla.code     = glc.GLCLASSIFICATIONLINKS_GLACCOUNT_CODE_ATTR
and    gla.division = glc.division_code
where  gla.code     = '4001'
and    gla.division = 12345

The result I expect back is 1 row but the query retrieves 12 rows.

A single query from the separate tables does give the expected result of 1 row. e.g.:

select glc.glclassification_code_attr
,      glc.glclassification_description
,      glc.GLCLASSIFICATIONLINKS_GLACCOUNT_CODE_ATTR
,      glc.division_code
from   ExactOnlineXML.XML.GLAccountClassifications glc
where  glc.GLCLASSIFICATIONLINKS_GLACCOUNT_CODE_ATTR = '4001'
and    glc.division_code                             = 12345

and:

select gla.Code
,      gla.Description
,      gla.Division
from   ExactOnlineREST..GLAccounts gla
where  gla.code     = '4001'
and    gla.division = 12345

How do I ensure that the combined query returns just 1 row as expected for division 12345?

Answer 1

Sorry it took a while to get on top of this question.

The question you ask is actually a bug that was in our software for some time. It only applies to queries where you have a join with multiple conditions which were all field comparisons. For a serious performance optimization (using something similar to an index for filtering), we were taking some shortcuts here and there. This resulted in only the first condition to be validated. Others weren't.

I will make sure this gets resolved using the normal channels.