This generate SQL with relations on DataAreaId between tables. Is possible to disable this relations?
SELECT A.PURCHID, A.DATAAREAID, A.RECID
FROM PURCHTABLE A
WHERE ((A.DATAAREAID IN (?,?) )
( SELECT ‘x’
FROM SALESTABLE B
WHERE ((B.DATAAREAID IN (?,?) )
AND (B.DATAAREAID = A.DATAAREAID))
AND (B.DATAAREAID = A.DATAAREAID)))))
Even though this problem may not be relevant to you any longer, but I will post a reply which may be beneficial to someone else.
I was also struggling with the same issue: the kernel was adding the (B.DATAAREAID = A.DATAAREAID) clause automatically if it detected a join by some fields between those tables. In your case it is
salesTable.SalesId == purchTable.InterCompanySalesId that causes the problem.
If you would change this line to something like salesTable.SalesId == ‘SO-00001’, the kernel would not add the (B.DATAAREAID = A.DATAAREAID) clause and the query would return (some) result.
This discovery lead me to the following workaround: it is possible to “fool” the kernel by changing your select statement in the following way:
salesTable.SalesId == purchTable.InterCompanySalesId+’’.
Now I wish I could figure out a workaround for the same problem in a Query…