While Select Query porting to Dynamic Query

Finance and Operations/AX Developer Forum
1

Hello Friends,

I have struggled with a Query written in embedded SQL and converting it into Query API.

Query in pure SQL is below:

while select custTable

where custTable.AccountNum == ‘1102’

join priceDiscTable

where priceDiscTable.AccountRelation == custTable.AccountNum

join inventTable

where inventTable.ItemId == priceDiscTable.ItemRelation

join inventTableModule

where inventTableModule.ItemId == inventTable.ItemId &&

inventTableModule.ModuleType == 2 &&

inventTableModule.UnitId == ‘ea’

{

…

}

and when I try to convert it into Query API it is as follows but doesn’t work as upper pure SQL one.

static void a0_DynamicQueryJob(Args _args)

{

Query query;

QueryRun queryRun;

QueryBuildDataSource custTableDS;

QueryBuildDataSource priceDiscTableDS;

QueryBuildDataSource inventTableDS;

QueryBuildDataSource inventTableModuleDS;

QueryBuildRange queryBuildRange;

QueryBuildRange queryBuildRange2;

CustTable custTable;

PriceDiscTable priceDiscTable;

InventTable inventTable;

InventTableModule inventTableModule;

PriceDisc priceDisc;

;

query = new Query();

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

SysQuery::findOrCreateRange(custTableDS, fieldnum(CustTable, AccountNum)); // In SysQueryForm 1102 is given

priceDiscTableDS = custTableDS.addDataSource(tableNum(PriceDiscTable));

priceDiscTableDS.relations(true);

priceDiscTableDS.joinMode(JoinMode::InnerJoin);

priceDiscTableDS.addLink(fieldnum(CustTable, AccountNum), fieldnum(PriceDiscTable, AccountRelation));

inventTableDS = priceDiscTableDS.addDataSource(tableNum(InventTable));

inventTableDS.relations(false);

inventTableDS.joinMode(joinMode::InnerJoin);

inventTableDS.addLink(fieldnum(PriceDiscTable, AccountRelation), fieldnum(InventTable, ItemId));

inventTableModuleDS = inventTableDS.addDataSource(tableNum(InventTableModule));

inventTableModuleDS.relations(false);

inventTableModuleDS.joinMode(joinMode::InnerJoin);

queryBuildRange = inventTableModuleDS.addRange(fieldnum(InventTableModule, ModuleType));

queryBuildRange.value(SysQuery::value(“2”));

queryBuildRange = inventTableModuleDS.addRange(fieldnum(InventTableModule, UnitId));

queryBuildRange.value(SysQuery::value(“ea”));

inventTableModuleDS.addLink(fieldnum(InventTable, ItemId), fieldnum(InventTableModule, ItemId));

queryRun = new SysQueryRun(query);

queryRun.prompt();

while(queryRun.next())

{

custTable = queryRun.get(tablenum(CustTable));

priceDiscTable = queryRun.get(tablenum(PriceDiscTable));

inventTable = queryRun.get(tablenum(InventTable));

inventTableModule = queryRun.get(tablenum(InventTableModule));

info(strfmt("%1 %2 %3 %4 %5 %6",

custTable.AccountNum,

priceDiscTable.price(),

priceDiscTable.Percent1,

priceDiscTable.Percent2,

inventTable.Itemid,

inventTable.ItemName));

}

}

Upper pure SQL query returns multiple rows which is right I thing but Query below returns only one row which is wrong.

Can anyone notice what I’m doing wrong?

Thanks for all,

Michael

2

You should always check the query generated by the tree of Query* objects, otherwise it’s like coding with closed eyes. Run this code after prompt():

info(queryRun.query().dataSourceNo(1).toString());

You’ll get this query:

SELECT FIRSTFAST * FROM CustTable
WHERE ((AccountNum = N'1102'))

JOIN FIRSTFAST * FROM PriceDiscTable
WHERE 1 = PriceDiscTable.Module
AND 0 = PriceDiscTable.AccountCode
AND CustTable.AccountNum = PriceDiscTable.AccountRelation
AND CustTable.AccountNum = PriceDiscTable.AccountRelation

JOIN FIRSTFAST * FROM InventTable
WHERE PriceDiscTable.AccountRelation = InventTable.ItemId

JOIN FIRSTFAST * FROM InventTableModule
WHERE InventTable.ItemId = InventTableModule.ItemId
AND ((ModuleType = 2))
AND ((UnitId = N'ea'))

You can see several things:

  • You join InventTable on PriceDiscTable.AccountRelation instead of PriceDiscTable.ItemRelation
  • The join between CustTable and PriceDiscTable is not what you tried to write
  • Your original query ignores some parts of relations, e.g. AccountCode. For example, if there is customer group with the same ID as a customer ID, your original query will return wrong results.

I would rewrite it to this:

Query                query;
QueryRun             queryRun;
QueryBuildDataSource custTableDS;
QueryBuildDataSource priceDiscTableDS;
QueryBuildDataSource inventTableDS;
QueryBuildDataSource inventTableModuleDS;
;

query = new Query();

custTableDS = query.addDataSource(tableNum(CustTable));
custTableDS.addRange(fieldnum(CustTable, AccountNum));

priceDiscTableDS = custTableDS.addDataSource(tableNum(PriceDiscTable));
priceDiscTableDS.relations(true);

inventTableDS = priceDiscTableDS.addDataSource(tableNum(InventTable));
inventTableDS.relations(true);

inventTableModuleDS = inventTableDS.addDataSource(tableNum(InventTableModule));
inventTableModuleDS.relations(true);
inventTableModuleDS.addRange(fieldnum(InventTableModule, ModuleType))
    .value(queryValue(ModuleInventPurchSales::Sales));
inventTableModuleDS.addRange(fieldnum(InventTableModule, UnitId))
    .value(queryValue("ea"));

queryRun = new SysQueryRun(query);
queryRun.prompt();

info(queryRun.query().dataSourceNo(1).toString());
3

Thanks again Martin!

Br

Michael