67
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 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)?
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));
}
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
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
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]'
#1
André Arnaud de Cal...
291,883
Super User 2024 Season 2
#2
Martin Dráb
230,569
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
