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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 25.08.2017, 19:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,574 / 848 (80) +++++++
Регистрация: 28.10.2006
stoneridgesoftware: X++ Select Statements That Look More Like SQL
Источник: https://stoneridgesoftware.com/x-sel...more-like-sql/
==============

As I’ve progressed as an AX developer, I’ve had to lean on many of the skills that I had from a former job as a C# developer where I used a lot of SQL queries. Working with data in X++ is similar until you try to write it like it would be written in SQL.

I would like to explain a quick tip that I got when working with multiple joins recently in X++,  as well as some other best practices when working with data.

Working with vendTrans and ProjTable, I needed to join a few tables to get to the data that I needed.  As I stated above, I came from a very SQL query heavy development environment, so my first step when working with data like this is to write it in SQL.



SQL Statement:

select p.Name, p.Workerresponsible, p.custAccount, v.ProjId, vit.*, from PSAPwpInvoiceTransView as v inner join VendInvoiceTrans as vit on vit.RecId = v.VendInvoiceTransRecId inner join VendTrans as vt on vit.InvoiceId = vt.invoice inner join ProjTable as p on p.ProjId = v.projId where vt.voucher = '#########' First X++ select:

select vendTrans join vendInvoiceTrans join ProjId from view join Name, WorkerResponsible, CustAccount from projTable where projTable.ProjId ==view.ProjId && vendTrans.voucher == '#########' && vendInvoiceTrans.InvoiceId == vendTrans.invoice && vendInvoiceTrans.RecId == view.VendInvoiceTransRecId As you can see above, I have all of the same joins and fields selected. There are definite differences in the languages that you cannot get around, but the tip that I received allows you to better see and understand were your joins are and where you might have an issue.

Easier to read X++:

select firstonly vendTrans where vendTrans.voucher == '#########' exists join vendInvoiceTrans where vendInvoiceTrans.InvoiceId == vendTrans.invoice exists join view where vendInvoiceTrans.RecId == view.VendInvoiceTransRecId exists join projTable where projTable.ProjId ==view.ProjId

More X++ Select Statement Tips:

1. Place the where clauses for each join under the line adding the join and table.

This gives you a better view of which fields are being joined, and helps with debugging if you have issues. This also give you a more “SQL-Like” visual of the select statement.

2. Make sure that you are using the correct joins. 

As seen above, I had inner joins on all of the tables and was selecting fields that I thought I needed.  After some testing, I realized the best practice is using Exists Joins, as then I did not need the fields from the joining tables and I only needed to make sure they existed in the table.  This speeds up the select as well as returns only the data that you need from VentTrans.

3. Use field groups to select ONLY the values that you need. 

As seen in the first X++ select that I wrote, I added a few of the fields that I needed from each table. Example:

join Name, WorkerResponsible, CustAccount from projTable This will return only the fields stated, whereas without calling them out you would get all fields from the projTable.

4. First only and First fast. 

In the second select code block, I added the firstOnly directive.  This directive speeds up the data retrieval by getting the first record that it needs. FirstFast was not a great solution to this as it may still incur overhead setting up the cursor to support additional results.  Therefore, if you know there is only a single row, use First only.



Источник: https://stoneridgesoftware.com/x-sel...more-like-sql/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
Старый 28.08.2017, 10:09   #2  
trud is offline
trud
Участник
Лучший по профессии 2017
 
1,039 / 1630 (57) ++++++++
Регистрация: 07.06.2003
Записей в блоге: 1
Цитата:
programmer team lead, software development supervisor, and software development manager
О. совсем дела плохи, таких людей заставляют непонятные селекты писать.
следующий пост наверное будет про query
Старый 28.08.2017, 11:08   #3  
mazzy is offline
mazzy
Участник
Аватар для mazzy
Лучший по профессии 2015
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
Лучший по профессии 2011
Лучший по профессии 2009
 
29,472 / 4494 (208) ++++++++++
Регистрация: 29.11.2001
Адрес: Москва
Записей в блоге: 10
Цитата:
Сообщение от Blog bot Посмотреть сообщение
3. Use field groups to select ONLY the values that you need.
О... Какое интересное употребление термина Field group.
Во-первых, этот совет противоречит ранним бестпрактисам, где предписывалось указывать поля явно.

А во-вторых, в Аксапте непосредственно существует такая штука как Field group.
И в заметке говорится не о той штуке...

И почему groups во множественном числе.

Очень странно.


Цитата:
Сообщение от Blog bot Посмотреть сообщение
4. First only and First fast.
интересно, автор знает об операторе next?
наверняка жеж знает... и просто ничего не сказал об этом в своей заметке?


https://www.youtube.com/watch?v=fOAa8RhPl-o
__________________
полезное на axForum, github, vk, coub.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
stoneridgesoftware: Expedite Security Role Testing Assignments After a Code Move in Dynamics AX Blog bot DAX Blogs 0 26.05.2017 06:11
CRM DE LA CREME! CRM 4.0 Disaster Recovery Blog bot Dynamics CRM: Blogs 2 26.02.2016 08:23
fed: Two stories about inventory closing and SQL Locks Blog bot DAX Blogs 3 14.01.2014 11:53
NAV Team: Example of How to use SQL Tracing Feature to Profile AL Code Blog bot Dynamics CRM: Blogs 0 18.09.2012 20:43
gatesasbait: Dynamics Ax SQL statements (SQL Strings in DAx) Blog bot DAX Blogs 1 16.04.2008 06:55

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

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

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 05:09.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.