Dynamics AX 2012 Multiple join on same table using QueryBuildDataSource (qbds)

Multiple join on same table using QueryBuildDataSource (qbds) Dynamics AX 2012

Once I come across the situation where there I have to build query using QueryBuildDataSource(qbds) with multiple tables and even same table with multiple join. By the simple joins it populate wrong data output (multiple row for each single row in main table).

So below is the example how to achieve the proper output having multiple join on same table using qbds in Microsoft dynamics ax 2012

QueryBuildDataSource qbdsInventJournalTable,qbdsInventJournalTrans,qbdsInventdim,qbdsInventdim1;

   QueryRun    qr;

   Query       q;

   InventJournalTrans  ObjInventJournalTrans;

   InventDim           ObjInventDim,ObjInventDim1;

   

   

   q= new Query();

   qbdsInventJournalTable = q.addDataSource(tableNum(InventJournalTable));

   

   qbdsInventJournalTrans = qbdsInventJournalTable.addDataSource(tableNum(InventJournalTrans));

   qbdsInventJournalTrans.relations(true);

   

   qbdsInventdim = qbdsInventJournalTrans.addDataSource(tablenum(Inventdim));

   qbdsInventdim.joinMode(JoinMode::InnerJoin);

   qbdsInventdim.addLink(fieldNum(Inventdim,InventDimId),fieldNum(InventJournalTrans,InventDimId));

   qbdsInventdim.fetchMode(QueryFetchMode::One2One);

   qbdsInventdim1 = qbdsInventJournalTrans.addDataSource(tablenum(Inventdim));

   qbdsInventdim1.joinMode(JoinMode::InnerJoin);

   qbdsInventdim1.addLink(fieldNum(Inventdim,InventDimId),fieldNum(InventJournalTrans,ToInventDimId));

   qbdsInventdim1.fetchMode(QueryFetchMode::One2One);

   qr = new QueryRun(q);

   while(qr.next())

   {

       ObjInventJournalTrans = qr.get(tableNum(InventJournalTrans));

       ObjInventDim = qr.get(tableNum(InventDim),1);

       ObjInventDim1 = qr.get(tableNum(InventDim),2);

// code to insert on table

   }