17,225
Like
Share
ReportHi,
In my previous post, I provided code which use SysDa framework to implement while select loop and in this post will provide the code where it can be used for X++ query joins.
Note: CG_SysDaQueryJoins is a runnable class.
class CG_SysDaQueryJoins
{
/// <summary>
/// SysDaQueryObject - Using like while-select loop with fieldlist, required joins and where clause
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
InventDim inventDim;
SysDaQueryObject sysDaQuery = new SysDaQueryObject(SalesTable);
SysDaQueryObject salesLineJoin = new SysDaQueryObject(salesLine);
SysDaQueryObject inventDimJoin = new SysDaQueryObject(inventDim);
{
/// <summary>
/// SysDaQueryObject - Using like while-select loop with fieldlist, required joins and where clause
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
InventDim inventDim;
SysDaQueryObject sysDaQuery = new SysDaQueryObject(SalesTable);
SysDaQueryObject salesLineJoin = new SysDaQueryObject(salesLine);
SysDaQueryObject inventDimJoin = new SysDaQueryObject(inventDim);
/*
Effective X++ Statement -
while select RecId, SalesId from salesTable
where salesTable.SalesId == "0007"
join InventTransId from salesLine
where salesLine.SalesId == salesTable.SalesId
join InventDimId from inventDim
where inventDim.InventDimId == salesLine.InventDimId
{
}
*/
Effective X++ Statement -
while select RecId, SalesId from salesTable
where salesTable.SalesId == "0007"
join InventTransId from salesLine
where salesLine.SalesId == salesTable.SalesId
join InventDimId from inventDim
where inventDim.InventDimId == salesLine.InventDimId
{
}
*/
sysDaQuery.projection().add(fieldStr(SalesTable, SalesId));
sysDaQuery.projection().add(fieldStr(SalesTable, RecId));
sysDaQuery.whereClause(new SysDaEqualsExpression
(new SysDaFieldExpression(SalesTable, fieldStr(SalesTable, SalesId)),
new SysDaValueExpression("0007")));
salesLineJoin.projection().add(fieldStr(SalesLine, ItemId));
salesLineJoin.projection().add(fieldStr(SalesLine, InventTransId));
salesLineJoin.whereClause(new SysDaEqualsExpression
(new SysDaFieldExpression(salesLine, fieldStr(SalesLine, SalesId)),
new SysDaFieldExpression(salesTable, fieldStr(SalesTable, SalesId))));
sysDaQuery.joinClause(SysDaJoinKind::InnerJoin, salesLineJoin);
sysDaQuery.projection().add(fieldStr(SalesTable, RecId));
sysDaQuery.whereClause(new SysDaEqualsExpression
(new SysDaFieldExpression(SalesTable, fieldStr(SalesTable, SalesId)),
new SysDaValueExpression("0007")));
salesLineJoin.projection().add(fieldStr(SalesLine, ItemId));
salesLineJoin.projection().add(fieldStr(SalesLine, InventTransId));
salesLineJoin.whereClause(new SysDaEqualsExpression
(new SysDaFieldExpression(salesLine, fieldStr(SalesLine, SalesId)),
new SysDaFieldExpression(salesTable, fieldStr(SalesTable, SalesId))));
sysDaQuery.joinClause(SysDaJoinKind::InnerJoin, salesLineJoin);
inventDimJoin.projection().add(fieldStr(InventDim, InventDimId));
inventDimJoin.whereClause(new SysDaEqualsExpression
(new SysDaFieldExpression(salesLine, fieldStr(SalesLine, SalesId)),
new SysDaFieldExpression(salesTable, fieldStr(SalesTable, SalesId))));
sysDaQuery.joinClause(SysDaJoinKind::InnerJoin, salesLineJoin);
inventDimJoin.whereClause(new SysDaEqualsExpression
(new SysDaFieldExpression(salesLine, fieldStr(SalesLine, SalesId)),
new SysDaFieldExpression(salesTable, fieldStr(SalesTable, SalesId))));
sysDaQuery.joinClause(SysDaJoinKind::InnerJoin, salesLineJoin);
SysDaSearchObject searchObject = new SysDaSearchObject(sysDaQuery);
SysDaSearchStatement searchStmt = new SysDaSearchStatement();
SysDaSearchStatement searchStmt = new SysDaSearchStatement();
while (searchStmt.nextRecord(searchObject))
{
info(strFmt("SalesTable: %1, SalesLine: %2, InventDim: %3", salesTable.SalesId, salesLine.ItemId, inventDim.inventDimId));
}
}
{
info(strFmt("SalesTable: %1, SalesLine: %2, InventDim: %3", salesTable.SalesId, salesLine.ItemId, inventDim.inventDimId));
}
}
}
Output: https://drive.google.com/open?id=1PWJ_yC6bW26KC6zJNSTggwbHSm4jpXXG
Regards,
Chaitanya
*This post is locked for comments