Monday 5 November 2012

Invalid use of the DataAreaId field in a cross-company query

Did a search online and doesn't find much info about this error - "Invalid use of the DataAreaId field in a cross-company query", it might be worth to share what has been found and the workaround.

During the development today, I hit this error, not sure why it doesn't allow the use of DataAreaId in the query criteria on crossCompany query, but there's a quick workaround for this - join it to DataArea table.



The query above had a compile error at 'tableA.dataAreaId' with an error message of "Invalid use of the DataAreaId field in a cross-company query". To workaround this error, joining it to DataArea table would fix it.

Instead of explaining in plain text, it is easier to understand by seeing the code and result.
Below are the sample data and the query to help further elaborate it.

TABLEA
DATAAREAID FIELD1     FIELD2     FIELD3     FIELD4
---------- ---------- ---------- ---------- ----------
ceu        CEU_R1_F1  CEU_R1_F2  CEU_R1_F3  CEU_R1_F4
ceu        CEU_R2_F1  CEU_R2_F2  CEU_R2_F3  CEU_R2_F4
con        CON_R1_F1  CON_R1_F2  CON_R1_F3  CON_R1_F4
con        CON_R2_F1  CON_R2_F2  CON_R2_F3  CON_R2_F4
dat        DAT_R1_F1  DAT_R1_F2  DAT_R1_F3  DAT_R1_F4
dat        DAT_R2_F1  DAT_R2_F2  DAT_R2_F3  DAT_R2_F4

TABLEB
DATAAREAID FIELD1     FIELD2     FIELD3     FIELD4
---------- ---------- ---------- ---------- ----------
ceu        F1         F2         F3         F4
con        F1         F2         F3         F4
dat        F1         F2         F3         F4


Work around by joining to DataArea table
while select crossCompany tableA
        join dataArea
       where dataArea.id       == tableA.dataAreaId
        join tableB
       where tableB.dataAreaId == dataArea.id
{
    info(strFmt("%1 - %2 - %3", tableA.dataAreaId,
                                tableA.Field1,
                                tableB.dataAreaId));
}

ceu - CEU_R1_F1 - ceu
ceu - CEU_R2_F1 - ceu
con - CON_R1_F1 - con
con - CON_R2_F1 - con
dat - DAT_R1_F1 - dat
dat - DAT_R2_F1 - dat


Incorrect join
Although TableB has 1 record per company, but joining with crossCompany keyword will result in joining without DataAreaId and cause incorrect query result.

while select crossCompany tableA
        join tableB
{
    info(strFmt("%1 - %2 - %3", tableA.dataAreaId,
                                tableA.Field1,
                                tableB.dataAreaId));
}

ceu - CEU_R1_F1 - ceu
ceu - CEU_R2_F1 - ceu
con - CON_R1_F1 - ceu
con - CON_R2_F1 - ceu
dat - DAT_R1_F1 - ceu
dat - DAT_R2_F1 - ceu
ceu - CEU_R1_F1 - con
ceu - CEU_R2_F1 - con
con - CON_R1_F1 - con
con - CON_R2_F1 - con
dat - DAT_R1_F1 - con
dat - DAT_R2_F1 - con
ceu - CEU_R1_F1 - dat
ceu - CEU_R2_F1 - dat
con - CON_R1_F1 - dat
con - CON_R2_F1 - dat
dat - DAT_R1_F1 - dat
dat - DAT_R2_F1 - dat



1 comment: