AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 08.02.2008, 00:10   #1  
Blog bot is offline
Blog bot
Участник
 
25,643 / 848 (80) +++++++
Регистрация: 28.10.2006
gatesasbait: Reverse keyword on Select Statements
Источник: http://gatesasbait.spaces.live.com/B...B9F5!190.entry
==============

 
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.
 
 
Finally 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)
 
 
That's all folks!


Источник: http://gatesasbait.spaces.live.com/B...B9F5!190.entry
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
gatesasbait: How to temporarily suppress infolog messages in Dynamics Ax (deux) Blog bot DAX Blogs 7 28.01.2009 17:45
2 while select или join? _scorp_ DAX: Программирование 9 23.01.2009 16:02
Разница NotInTTS и Found Logger DAX: База знаний и проекты 6 18.09.2008 12:35
gatesasbait: Dynamics Ax SQL statements (SQL Strings in DAx) Blog bot DAX Blogs 1 16.04.2008 06:55
Вопрос про Demand Planner slava09 DAX: Функционал 4 25.09.2006 11:43

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 08:43.