Reverse keyword on Select Statements

 

Reverse keyword on Select Statements

 

Good afternoon,

 

Let me get right to the point. When you are browsing a Form that contains a lot of records, the records are loaded progressively on scrolling. The query built by the form's Datasources uses the keyword 'FirstFast' and that is why you can open up forms faster than it takes for a report with a similar query to run.

 

So what happens when you pick the scroll button with your mouse, and move it right to the bottom of the scrollbar? The records are scrolled UP TO the last record that was fetched previously with the 'FirstFast', no additional records are actually fetched until you release the scroll button. If you happen to release the scroll button at the absolute maximum position of the scrollbar, Dynamics Ax will clear its resultset and execute a new 'Reversed Query' fetching the records starting with the last record in the table, and you will basically have scrolled to the last record. As you scroll back up, the records are once again progressively fetched, but in reverse order.

 

However, the 'reverse' keyword does not exist on Query objects, only on select statements. It actually doesn't exist in SQL at all. So what does Ax do?

 

Anytime records are fetched using a Query object, the SQL statement generated will hold an order by clause, for example:

 

public static void simpleQuery(Args _args)
{
    Query q;
    QueryRun qr;
    QueryBuildDataSource qbds;
    ;

 

    q = new Query();
    qbds = q.addDataSource(tablenum(SalesLine));
    qr = new queryrun(q);

    while (qr.next())
    {
   
    }
}

 

will generate the following SQL Statement:

 

SELECT * FROM SALESLINE A

WHERE (DATAAREAID=?)

ORDER BY A.DATAAREAID,A.SALESID,A.LINENUM,A.RECID

 

Where did that 'order by' come from?

 

By default, Dynamics Ax sorts all Queries with the table index returned by:

 

public static void showFirstIndex(Args _args)
{
    DictTable dictTable;
    ;

    dictTable = new DictTable(tablenum(SalesLine));
    info(dictTable.indexName( dictTable.indexCnt2Id(1)));
}

 

Output:
"SalesLineIdx"

 

to determine which order to use when returning rows from a Query. Reverse is implemented (by Forms only) on Queries simply by specifying that that index is to be sorted Descending instead of Ascending:

 

public static void simpleWhileSelectReverse(Args _args)
{
    SalesLine salesLine;
    ;

    while select reverse salesLine
    {

    }
}

 

this job generates the following SQL Statement:

 

SELECT * FROM SALESLINE A

WHERE (DATAAREAID=?)

ORDER BY A.DATAAREAID DESC,A.SALESID DESC,A.LINENUM DESC,A.RECID DESC

 

Note that the order by clause is not added to while select statements when the 'reverse' keyword is not used. An order by clause is added however on Queries, always. Also note that if no index exists on the queried table, dictTable.indexCnt2Id(1) will return ' RecId' regardless of the value of property 'createRecIdIndex' on the table.

 

 Here's what the SQL statement looks like when opening up the InventTrans Form (note that OPTION(FAST 1) is FIRSTFAST in Ax):

SELECT * FROM INVENTTRANS A,
WHERE (A.DATAAREAID=?)

ORDER BY A.DATAAREAID,A.INVENTDIMID,A.ITEMID OPTION(FAST 1)

 

Here's what it looks like when scrolled all the way down:

SELECT * FROM INVENTTRANS A,
WHERE (A.DATAAREAID=?)

ORDER BY A.DATAAREAID DESC,A.INVENTDIMID DESC,A.ITEMID DESC OPTION(FAST 1)

 

Here's how we might implement a 'reversed Query':

 

static void simpleReverseQuery(Args _args)
{
    Query q;
    QueryRun qr;
    QueryBuildDataSource qbds;
    SalesLine salesLine;
   
    DictTable dictTable;
    DictIndex dictIndex;
   
    int i = 0;
    ;

    dictTable = new DictTable(tablenum(SalesLine));
    dictIndex = new DictIndex(tablenum(SalesLine), dictTable.indexCnt2Id(1));

    q = new Query();
    qbds = q.addDataSource(tablenum(SalesLine));
   
    for (i = 1; i <= dictIndex.numberOfFields() ; i++)
    {
        qbds.addSortField(dictIndex.field(i), SortOrder::Descending);
    }

    qr = new queryrun(q);

    while (qr.next())
    {
        salesLine = qr.get(tablenum(SalesLine));
        info(salesLine.InventTransId);
    }
}