X++ Query in tree joining pattern

Generally X++ query can be created in a linear joining pattern for example

SalesTable - SalesLine - InventTable

However sometimes we would like write a query in a tree joining pattern as follows.

For that case, we can apply the code as the following way.

void TreeJoinExample()
{
    Query                   q;
    QueryBuildDataSource    qbdsSalesLine,
                            qbdsSalesTable,
                            qbdsInventTable;
    ;


    q = new Query();
    
    qbdsSalesLine = q.addDataSource(tableNum(SalesLine));

    qbdsSalesTable = qbdsSalesLine.addDataSource(tableNum(SalesTable));
    qbdsSalesTable.relations(false);
    qbdsSalesTable.addLink(fieldNum(SalesLine, SalesId), fieldNum(SalesTable, SalesId));
    qbdsSalesTable.fetchMode(queryFetchMode::One2One);

    qbdsInventTable = qbdsSalesLine.addDataSource(tableNum(InventTable));
    qbdsInventTable.relations(false);
    qbdsInventTable.addLink(fieldNum(SalesLine, ItemId), fieldNum(InventTable, ItemId));
    qbdsInventTable.fetchMode(queryFetchMode::One2One);
}

X++ Union query

Let's see this scenario.

Condition: We'd like to have the following results. 1) all sales lines which has all status, except invoiced and cancelled and 2) all sales lines which has status = invoiced, and invoiceDate(in CustInvoiceJour) >= today

For above purpose, we can apply an union query like below.

void initQuery()
{
    Query                   query;
    QueryBuildDataSource    qbdsSalesLine,
                            qbdsCustInvoiceJour;
    ;

    query               = new query();
    query.queryType(QueryType::Union);

    qbdsSalesLine       = query.addDataSource(tableNum(SalesLine), identifierstr(SalesLine_1));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Backorder));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Delivered));
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::None));

    qbdsSalesLine       = query.addDataSource(tablenum(SalesLine), identifierstr(SalesTable_2), UnionType::Union);
    qbdsSalesLine.addRange(fieldnum(SalesLine, SalesStatus)).value(queryValue(SalesStatus::Invoiced));

    qbdsCustInvoiceJour = qbdsSalesLine.addDataSource(tableNum(CustInvoiceJour));
    qbdsCustInvoiceJour.relations(false);
    qbdsCustInvoiceJour.addLink(fieldNum(SalesLine, SalesId), fieldNum(CustInvoiceJour, SalesId));
    qbdsCustInvoiceJour.joinMode(JoinMode::ExistsJoin);
    qbdsCustInvoiceJour.addRange(fieldnum(CustInvoiceJour, InvoiceDate)).value(SysQuery::range(today(), dateMax()));

    queryrun = new SysQueryRun(query);
}

Note that:

Union – remove duplicated records

UnionAll – keep duplicated records

AX2012 Location of table CompanyInfo in SQL Server

Recently, I had a serious problem about field dataAreaId. The issue is when creating a new record on InventTable, It always got a wrong dataAreaId. (For example, I hope it created 'c01', but it did 'c99' instead.)

After check, I found that method initValue() generate that dataAreaId from somewhere else which not in table CompanyInfo.

So it would save a lot of time if we know the exact table in SQL Server which kept AX's table CompanyInfo.

Here it's.
SQL Server ---> table 'DataArea' or view 'CompanyView'
AX             ---> table 'CompanyInfo'

From above, I can get the cause of this issue. The wrong dataAreaId came from the virtual company and table collection setting cause.

Have a good day!