AX2012 cross company query challenge and workaround

Had an encounter with cross company query in AX2012 and listed below are the findings. When we issue the cross company clause there are a few important things that happen.

1. The sql query at the backend is converted into a cross join for most of the joins.
2. The sql query at the backend automatically created conditions for the partions and dataareaids to ensure that cross joins dont distort the data across companies.

Take an example of an select statement as below:-

SELECT crosscompany count(RecId) FROM TSTimesheetLine
join tsTimesheetTable
where TSTimesheetTable.TimesheetNbr == TSTimesheetLine.TimesheetNbr
    && tsTimesheetLine.ProjId != 'Z905'
join TSTimesheetLineWeek
where TSTimesheetLineWeek.TSTimesheetLine == TSTimesheetLine.RecId
&& ( ( ( TsTimesheetLineWeek.Hours[0] + TsTimesheetLineWeek.Hours[1] + TsTimesheetLineWeek.Hours[2] + TsTimesheetLineWeek.Hours[3] + TsTimesheetLineWeek.Hours[4] + TsTimesheetLineWeek.Hours[5] + TsTimesheetLineWeek.Hours[6] + TsTimesheetLineWeek.Hours[7] ) > 0 ) )
notExists join supportHrsView  

where supportHrsView.TsTimesheetLineRef == TSTimesheetLine.RecId 

Now, my requirement is to match the highlighted condition across the dataAreaId as the supportHrsView is a shared table for me. Basically i dont want the compiler to apply a dataAreaId condition on this table. 

This statement is translated to SQL as follows
SELECT COUNT(T1.RECID) 
FROM TSTIMESHEETLINE T1 
CROSS JOIN TSTIMESHEETTABLE T2 
CROSS JOIN TSTIMESHEETLINEWEEK T3 
WHERE (T1.PARTITION=@P1) 
AND ((T2.PARTITION=@P2) AND ((T2.TIMESHEETNBR=T1.TIMESHEETNBR AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T1.PROJID<>@P3))) 
AND ((T3.PARTITION=@P4) AND ((T3.TSTIMESHEETLINE=T1.RECID AND (T3.DATAAREAID = T1.DATAAREAID) AND (T3.PARTITION = T1.PARTITION)) AND ((((((((T3.HOURS+T3.HOURS)+T3.HOURS2_)+T3.HOURS3_)+T3.HOURS4_)+T3.HOURS5_)+T3.HOURS6_)+T3.HOURS7_)>@P5))) 
AND NOT (EXISTS 
(
SELECT 'x' FROM AFZSUPPORTHRSVIEW T4 
WHERE (
(T4.PARTITION=@P6) 
AND (T4.TSTIMESHEETLINEREF=T1.RECID AND (T4.DATAAREAID = T1.DATAAREAID) AND (T4.PARTITION = T1.PARTITION))
)
)

)

Please note the following facts:

1. All the joins in the query are translated to cross joins. 

2. There are 4 set of data in this and these have an alias as T1, T2, T3 and T4

As we know that cross joins results into cartisan product of the two tables it can result in a huge result set and hence the compiler takes care to ensure that the data is not mixed up between the different sets by enforcing a partition and dataarea id condition even though it is not explicitly provided in the select statement. 

1. Please note that there is a partition condition applied for each of the resultsets T1 to T4. 

2. Please note that there is a dataAreaId condition applied for each resultset where the dataAreaId for T1 is applied on T2, T3, and T4

Coming back to what my requirement is i want a way to ensure that the dataAreaId condition applied on T4 (T4.DATAAREAID = T1.DATAAREAID) is skipped. The workaround to get around this is to apply an operator on the join with supportHrsView, so if i change my condition in the select query as follows:

where supportHrsView.TsTimesheetLineRef == TSTimesheetLine.RecId + 0 

Now, the compiler skips the forced dataAreaId and Partition join and the SQL query issued by the compiler to SQL is as follows: 

SELECT COUNT(T1.RECID) 
FROM TSTIMESHEETLINE T1 
CROSS JOIN TSTIMESHEETTABLE T2 
CROSS JOIN TSTIMESHEETLINEWEEK T3 
WHERE (T1.PARTITION=@P1) 
AND ((T2.PARTITION=@P2) AND ((T2.TIMESHEETNBR=T1.TIMESHEETNBR AND (T2.DATAAREAID = T1.DATAAREAID) AND (T2.PARTITION = T1.PARTITION)) AND (T1.PROJID<>@P3))) 
AND ((T3.PARTITION=@P4) AND ((T3.TSTIMESHEETLINE=T1.RECID AND (T3.DATAAREAID = T1.DATAAREAID) AND (T3.PARTITION = T1.PARTITION)) AND ((((((((T3.HOURS+T3.HOURS)+T3.HOURS2_)+T3.HOURS3_)+T3.HOURS4_)+T3.HOURS5_)+T3.HOURS6_)+T3.HOURS7_)>@P5))) 
AND NOT (EXISTS 
(
SELECT 'x' 
FROM AFZSUPPORTHRSVIEW T4 
WHERE ((T4.PARTITION=@P6) AND (T4.TSTIMESHEETLINEREF=(T1.RECID+@P7)))
)
)

Please note that the condition T4.DATAAREAID = T1.DATAAREAID is now not applied and we get the desired results.