How to show zero values in Inner Join for Dynamics AX 2012?

Question

I have two tables that I've connected by Inner Join

qbdsPurchTable = query.addDataSource(tableNum(PurchTable));

qbdsVendPackingSlipTrans = qbdsPurchTable.addDataSource(tableNum(VendPackingSlipTrans));
qbdsVendPackingSlipTrans.fetchmode(Joinmode::InnerJoin);
qbdsVendPackingSlipTrans.addLink(fieldNum(PurchTable, PurchId),fieldNum(VendPackingSlipTrans, OrigPurchId));
qbdsVendPackingSlipTrans.relations(false);

In my purch table, it has a lot of data but only one has a data on the VendPackingSlipTrans.

Purch Table      Vend Packing Slip Trans
PurchId             PurchId     Price
data1                 data2        1000
data2
data3
data4

What my code is doing is that:
data2 1000

but what i want to happen is that
data1 0
data2 1000
data3 0
data4 0

by the way, I tried outer join but this is the output
data1 0
data2 0
data3 0
data4 0

it doesn't get the 1000 value.

Could you post the SQL statement of the query? Please edit it into your question instead of writing a comment. — FH-Inway

ulisses ulisses · Accepted Answer · 2017-03-20T10:10:49

Be carefully if you use InnerJoin, the query returns the record with only relation having TRUE, so, it's right to having only one record (you've only match between record).

Try to use outerjoin

qbdsPurchTable = query.addDataSource(tableNum(PurchTable));

qbdsVendPackingSlipTrans = qbdsPurchTable.addDataSource(tableNum(VendPackingSlipTrans));
qbdsVendPackingSlipTrans.joinMode(Joinmode::OuterJoin);
qbdsVendPackingSlipTrans.addLink(fieldNum(PurchTable, PurchId),fieldNum(VendPackingSlipTrans, OrigPurchId));
qbdsVendPackingSlipTrans.relations(false);

Here is some additional information:

http://bmdax.blogspot.it/2011/05/joins-inner-outer-combined.html;
http://dynamicsaxhari.blogspot.it/2014/09/form-data-source-link-types-active.html
http://www.to-increase.com/ax-fetch-mode/
https://community.dynamics.com/ax/f/33/t/135626

New link: http://theaxapta.blogspot.it/2013/05/multiple-tables-in-query.html

The new code - select all record :

QueryRun queryRun;
PurchTable purchTable;
VendPackingSlipTrans vendPackingSlipTrans;
QueryBuildDataSource datasource; 
Query query;

query = new query();

datasource = query.addDataSource(tableNum(PurchTable));
datasource = datasource.addDataSource(tableNum(VendPackingSlipTrans));
datasource.joinMode(JoinMode::OuterJoin);
datasource.relations(true);

queryRun = new QueryRun(query);
while (queryRun.next()) // insert this while loop to test the code
{
    purchTable = queryRun.get(tablenum(PurchTable));
    vendPackingSlipTrans = queryRun.get(tablenum(VendPackingSlipTrans));

    info(strfmt("%1 – %2", purchTable.PurchId, vendPackingSlipTrans.Price)); // insert yours fields
}

I show all value.

How to show zero values in Inner Join for Dynamics AX 2012?

2 Answers