Building queries with fetchmode property in AX 2012

static void custTableSelect(Args _args)

{

   CustTable   custTable;

   CustGroup   custGroup;

   DlvTerm     dlvTerm;

   #define.custGroup('Net30')

   #define.dlvTerm(LogisticsLocationRoleType::None)

   while select custTable

       join custGroup

           where custGroup.CustGroup == custTable.CustGroup

              && custGroup.PaymTermId == #custGroup

       join dlvTerm

           where dlvTerm.Code == custTable.DlvTerm

              && dlvTerm.TaxLocationRole == #dlvTerm

   {

       info(custTable.AccountNum);

   }

}

We can use standard QueryBuildDataSource classes:

static void custTableQuery(Args _args)

{

   CustTable               custTable;

   QueryBuildDataSource    dsCustTable, dsCustGroup, dsDlvTerm;

   Query                   query = new Query();

   QueryRun                queryRun;

   #define.custGroup('Net30')

   #define.dlvTerm(LogisticsLocationRoleType::None)

   dsCustTable = query.addDataSource(tableNum(CustTable));

   dsCustGroup = dsCustTable.addDataSource(tableNum(CustGroup));

   dsCustGroup.addLink(fieldNum(CustTable, CustGroup), fieldNum(CustGroup, CustGroup));

   dsCustGroup.addRange(fieldNum(CustGroup, PaymTermId)).value(#custGroup);

   dsDlvTerm = dsCustTable.addDataSource(tableNum(dlvTerm));

   dsDlvTerm.addLink(fieldNum(CustTable, DlvTerm), fieldNum(DlvTerm, Code));

   dsDlvTerm.addRange(fieldNum(dlvTerm, TaxLocationRole)).value(queryValue(#dlvTerm));

   queryRun = new queryRun(query);

   while (queryRun.next())

   {

      custTable =  queryRun.get(tableNum(custTable));

      info(custTable.AccountNum);

   }

}

The problem is that above code will not work properly. The built queries will be separated, not joined. To make it work as expected we have to use FetchMode property for QueryBuildDataSource. By default QueryBuildDataSource has property FetchMode == QueryFetchMode::One2Many. We need to set this property to FetchMode == QueryFetchMode::One2One. See below:

static void custTableQuery(Args _args)

{

   dsCustGroup = dsCustTable.addDataSource(tableNum(CustGroup));

   dsCustGroup.addLink(fieldNum(CustTable, CustGroup), fieldNum(CustGroup, CustGroup));

   dsCustGroup.addRange(fieldNum(CustGroup, PaymTermId)).value(#custGroup);

   dsCustGroup.fetchMode(QueryFetchMode::One2One);

   dsDlvTerm = dsCustTable.addDataSource(tableNum(dlvTerm));

   dsDlvTerm.addLink(fieldNum(CustTable, DlvTerm), fieldNum(DlvTerm, Code));

   dsDlvTerm.addRange(fieldNum(dlvTerm, TaxLocationRole)).value(queryValue(#dlvTerm));

   dsCustGroup.fetchMode(QueryFetchMode::One2One);

}