X++ Select Statements That Look More Like SQL

by | Updated August 25, 2017 | Development, Dynamics AX

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:

First X++ select:

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++:

 

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:

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.

Related Posts

2 Comments

  1. Don Gillette

    Do you know of any refactor tools that convert SQL to X++?

    Thanks,

  2. Taylor Valnes

    Hello Don,

    That’s a great question, but we don’t know of any tools that would convert SQL to X++.

    Regards,
    Taylor

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

august

12aug10:00 am10:30 amWhy Levridge Grain? How to Achieve Efficient and Accurate Scale Tickets

12aug12:00 pm1:00 pmThe Three Paths to Dynamics 365 Finance and Supply Chain from Dynamics AX

13aug11:00 am12:00 pmConfab with Stoneridge - Livestream - Inspire Keynote Breakdown

19aug10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

19aug12:00 pm12:30 pmThe Modern Manufacturer - Death by Safety Stock

27aug11:00 am12:00 pmConfab with Stoneridge - Livestream - Dynamics 365 2020 Wave 2 Preview

september

02sep10:00 am10:30 amThe Modern Manufacturer - Cycle Count Management

09sep10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

16sep10:00 am10:30 amThe Modern Manufacturer - Product Lifecycle Management

30sep10:00 am10:30 amThe Modern Manufacturer - Return Management

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X