convert X++ Select Statement into aot query

(0) Share

Report

Posted on by Mahmood Ul Hassan

i want to convert following X++ statement into AOT query

while select ItemId,Name,Qty,LineAmount,PurchPrice,PurchUnit,InvoiceDate
from VendInvoiceTrans join PurchMaxItemPurchasePriceTMP
where VendInvoiceTrans.ItemId == PurchMaxItemPurchasePriceTMP.ItemId &&
VendInvoiceTrans.InvoiceDate == PurchMaxItemPurchasePriceTMP.MaxInvoiceDate

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

axk 925 on at

Like (0)

Report
Copy link

Link copied!

Follow these two documents and everything will be fine!

'How to: Create Queries by using the AOT [AX 2012]'

msdn.microsoft.com/.../bb314753.aspx

'How to: Add Multiple Data Sources to a Query [AX 2012]'

msdn.microsoft.com/.../aa880078.aspx

Was this reply helpful? Yes No
Mahmood Ul Hassan 69 on at

Like (0)

Report
Copy link

Link copied!

I have created Query in aot when i translate this AOT query into X++ in a job it gives me following resulted select statement.

SELECT ItemId, Name, Qty, LineAmount, PurchPrice, PurchUnit, PurchID, InvoiceDate FROM VendInvoiceTrans(VendInvoiceTrans) JOIN ItemId, MaxInvoiceDate FROM PurchMaxItemPurchasePriceTMP(PurchMaxItemPurchasePriceTMP) ON VendInvoiceTrans.InvoiceDate = PurchMaxItemPurchasePriceTMP.MaxInvoiceDate AND VendInvoiceTrans.ItemId = PurchMaxItemPurchasePriceTMP.ItemId

it is not my requirement as it didnt select any data from the tables. i want query that gives me this following select statement.

while select ItemId,Name,Qty,LineAmount,PurchPrice,PurchUnit,InvoiceDate
from VendInvoiceTrans join PurchMaxItemPurchasePriceTMP
where VendInvoiceTrans.ItemId == PurchMaxItemPurchasePriceTMP.ItemId &&
VendInvoiceTrans.InvoiceDate == PurchMaxItemPurchasePriceTMP.MaxInvoiceDate

Was this reply helpful? Yes No
axk 925 on at

Like (0)

Report
Copy link

Link copied!

Why didn't it select any data?

Statements are almost the same. How are you retrieving data from this query?

Here is an example how to retrieve records from AOT query:

daxbeginners.wordpress.com/.../how-to-use-aot-query-through-x

Was this reply helpful? Yes No
Mahmood Ul Hassan 69 on at

Like (0)

Report
Copy link

Link copied!

The Following job Returns data as i wanted

static void ItemPurchasePrice(Args _args)
{
PurchMaxItemPurchasePriceTMP PurchMaxItemPurchasePriceTMP;
VendInvoiceTrans VendInvoiceTrans;
PurchItemPurchasePriceTMP PurchItemPurchasePriceTMP;

insert_recordset PurchMaxItemPurchasePriceTMP(ItemId,MaxInvoiceDate)
select ItemId, maxof(InvoiceDate)
from VendInvoiceTrans
group by ItemId;

while select ItemId,Name,Qty,LineAmount,PurchPrice,PurchUnit,InvoiceDate
from VendInvoiceTrans join PurchMaxItemPurchasePriceTMP
where VendInvoiceTrans.ItemId == PurchMaxItemPurchasePriceTMP.ItemId &&
VendInvoiceTrans.InvoiceDate == PurchMaxItemPurchasePriceTMP.MaxInvoiceDate
{
PurchItemPurchasePriceTMP.clear();
PurchItemPurchasePriceTMP.ItemId = VendInvoiceTrans.ItemId;
PurchItemPurchasePriceTMP.InvoiceDate = VendInvoiceTrans.InvoiceDate;

info(strFmt("ItemId %1, MaxInvoiceDate %2",PurchItemPurchasePriceTMP.ItemId,PurchItemPurchasePriceTMP.InvoiceDate ));
}

}

But wrote another job with the AOT query but is doesnt return any data i dont know why???

static void ItemPurchasePrice3(Args _args)
{
PurchMaxItemPurchasePriceTMP PurchMaxItemPurchasePriceTMP;
VendInvoiceTrans VendInvoiceTrans;

Query query;
QueryBuildDataSource qbdsTrans,qbdsMax;
QueryBuildRange qbr;
QueryRun queryRun;
Counter totalRecords;

insert_recordset PurchMaxItemPurchasePriceTMP(ItemId,MaxInvoiceDate)
select ItemId, maxof(InvoiceDate)
from VendInvoiceTrans
group by ItemId;

query = new Query(queryStr(purchItemPurchasePriceQuery));
qbdsTrans = query.dataSourceTable(tableNum(VendInvoiceTrans));

queryRun = new QueryRun(query);

if (queryRun.prompt())

{

while (queryRun.next())

{
VendInvoiceTrans = queryRun.get(tableNum(VendInvoiceTrans));
PurchMaxItemPurchasePriceTMP = queryRun.get(tableNum(PurchMaxItemPurchasePriceTMP));

}

}

info(strFmt("ID %1", VendInvoiceTrans.ItemId));

}

Was this reply helpful? Yes No
Martin Dráb 238,534 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I think your problem is related to the temporary table and not the query; you put data to one buffer and then query another (which is empty).

Is PurchMaxItemPurchasePriceTMP a temporary table? Which type (InMemory or TempDB)?

Was this reply helpful? Yes No