在X++里怎么使用AOT中预定义的Query / How to: Use AOT Query through X++

This post will focus on one of the basics of AX, AOT Query. A query performs the same function as the select statements but it’s a better option because it allows for more flexible user interaction when defining which records are to be retrieved, it’s also a best practice to use it instead of select statement when the structure of the query is not known until run time.

For example, I need to retrieve information from CustInvoiceJour with invoice date from January 2012.
To help me on this scenario I will use the AOT query CustInvoiceJour.

Queries are embedded in Forms, Reports and RunBase jobs and should always be used with these application objects, unless there are good reasons not to. Today I will show how to use it on a Job, you will see that I can edit my AOT query through X++ as I wish.

static void _AOTquery(Args _args)
{
    date            dateFrom    = 01\01\2012 ;
    date            dateTo      = 31\01\2012 ;
    CustInvoiceJour custInvoiceJour;
    Query           query = new Query(queryStr (CustInvoiceJour)); // Query name.
    QueryRun        qr;
    QueryBuildRange qbr;
    ;
   
    // Find the InvoiceDate range on AOT Query.
    qbr = query.dataSourceTable( tablenum (CustInvoiceJour))
            .findRange( fieldNum (CustInvoiceJour, InvoiceDate));
   
    // We can check if the query range does exist, if not, we can create it.
    if (!qbr)
    {
        qbr = query.dataSourceTable( tableNum (CustInvoiceJour))
            .addRange( fieldNum (CustInvoiceJour, InvoiceDate));
    }
   
    // Assigning query range value.
    qbr.value(SysQuery::range(dateFrom, dateTo));
   
    // We can also define an Order By through code.
    query.dataSourceTable( tableNum (CustInvoiceJour))
        .addOrderByField( fieldNum (CustInvoiceJour, OrderAccount));
   
    // Executing our query.
    qr = new QueryRun(query);
   
    // Looping through query results.
    while (qr.next())
    {
        // Assinging query results to table buffer.
        custInvoiceJour = qr.getNo( 1 );
       
        // Showing results.
        info( strFmt ('%1 - %2 - %3' , custInvoiceJour.InvoiceDate,
                                    custInvoiceJour.SalesId,
                                    custInvoiceJour.OrderAccount));
    }
}

When I debug my job and check the QueryRun object, the query has been “translated” to a select statement:

SELECT CurrencyCode, EndDisc, InvoiceAccount, InvoiceAmount, InvoiceAmountMST, InvoiceDate, InvoiceId, InvoicingName, numberSequenceGroup, RecId, SumLineDisc, SumMarkup, SumTax, LedgerVoucher, DueDate, OrderAccount, SumTaxMST, SalesId
	FROM CustInvoiceJour(CustInvoiceJour) 
	ORDER BY CustInvoiceJour.InvoiceId ASC, CustInvoiceJour.InvoiceDate ASC, CustInvoiceJour.numberSequenceGroup ASC, CustInvoiceJour.OrderAccount ASC
		WHERE ((InvoiceDate>={ts '2012-01-01 00:00:00.000'} 
		AND InvoiceDate<={ts '2012-01-31 00:00:00.000'}))

And this is the results:

转载请注明：ww12345678 的部落格 | AX Helper » 在X++里怎么使用AOT中预定义的Query / How to: Use AOT Query through X++