My colleague asked my advise about
cross-company query. The goal was to create missing records of customer specific table in another company. The query looked correct, however the tests failed.
Problem description
To make it simple let's assume that records of
InventItemGroup table in company
USMF must be created in company
USRT (provided the records are missing in
USRT company). It seems to be possible to use
cross-company query like this:
InventItemGroup inventItemGroup, inventItemGroupJoin;
while select crossCompany inventItemGroup
where inventItemGroup.dataAreaId == 'USMF'
notExists join inventItemGroupJoin
where inventItemGroupJoin.dataAreaId == 'USRT'
&& inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId
{
// create new record in USRT company
}
|
This approach is wrong though. Are you also curious why? Let's analyse some specifics of
cross-company queries.
Steps to reproduce
The following job can be used for tests and analysis:
static void CrossCompanyTest(Args _args)
{
InventItemGroup inventItemGroup, inventItemGroupJoin;
select generateonly forceLiterals crossCompany ItemGroupId
from inventItemGroup
where inventItemGroup.dataAreaId == 'USMF'
notExists join inventItemGroupJoin
where inventItemGroupJoin.dataAreaId == 'USRT'
&& inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId;
info(inventItemGroup.getSQLStatement());
}
|
Hints
Try to modify predicates of join and use
Query class instead of select statement.
