CODEWITHSUNDEEP
axaptax++ax
Danielg
Danielg

Reputation: 2699

Outer joins with where condition x++

I am trying to write a query that retrieves an item based on ItemId or item barcode.

This is using x++, Dynamics AX 2012 R2

select firstOnly * from inventTable
where (inventTable.ItemId == _upc || inventItemBarcode.itemBarCode == _upc)
outer join inventItemBarcode 
where inventItemBarcode.itemId == inventTable.ItemId;

When this is translated into sql it comes out as...

FROM  INVENTTABLE T1 LEFT 
OUTER 
JOIN INVENTITEMBARCODE T2 ON (((T2.PARTITION=?) 
    AND (T2.DATAAREAID=?)) 
    AND (T1.ITEMID=T2.ITEMID)) 
WHERE (((T1.PARTITION=?) 
    AND (T1.DATAAREAID=?)) 
    AND (T1.ITEMID=?))

You can see that is is totally omitting the OR condition in the last line.

I tried writing the query like this

    select firstOnly * from inventTable
    outer join inventItemBarcode 
    where
        //join
        inventItemBarcode.itemId == inventTable.ItemId
        //ilc
        && (inventTable.ItemId == _upc
        || inventItemBarcode.itemBarCode == _upc);

But it puts the OR condition in the outer join ON and then returns me the first row in the InventTable.

Does anyone know how to make this work in X++?

Upvotes: 2

Views: 8021

Answers (1)

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

If using AX 2012 you will have to use Query and QueryRun instead, then add your or-expression as a query expression using addQueryFilter.

static void Job117(Args _args)
{
    str search = "5705050765989";
    QueryRun qr = new QueryRun(new Query());
    QueryBuildDataSource ds1 = qr.query().addDataSource(tableNum(InventTable));
    QueryBuildDataSource ds2 = ds1.addDataSource(tableNum(InventItemBarcode));
    str qstr1 = '((%1.%2 == "%5") || (%3.%4 == "%5"))';
    str qstr2 = strFmt(qstr1, ds1.name(), fieldStr(InventTable,ItemId), 
                       ds2.name(), fieldStr(InventItemBarcode,ItemBarCode),
                       queryValue(search));
    qr.query().addQueryFilter(ds1, fieldStr(InventTable,ItemId)).value(qstr2);
    ds2.joinMode(JoinMode::OuterJoin);
    ds2.relations(true);
    info(qstr2);
    info(ds1.toString());
    while (qr.next())
        info(strFmt("%1", qr.getNo(1).RecId));
}

In prior AX versions you can make a view, then query the view using a query expressions by using the addRange method.

Upvotes: 3

Related Questions