How To Write A While Select Statement

Any ERP system needs to process data. An x++ ‘while select statement’ allows developers to loop through specific records with ease. Then take action on those records. The x++ language in Microsoft Dynamics 365 for Finance and Operations combines the best of both SQL like language and object oriented programming. First, developers can efficiently tell the SQL database what records to retrieve. Then, load the data in table buffer variables. Finally, code from a high level language processes the data.

Differences Between While Select and Select Statement

Before proceeding further, it is important to understand the differences between a while select statement and a select statement. The short answer is that select statements are usually used to find only one record. Whereas, ‘while select’ statements are used to find one or more records.

In the next section, learn how to query a table and only one record. In these cases, use a ‘select statement’. However, when more than one record ‘could’ be returned based on the criteria you have, then use a ‘while select’ statement.

If you have not already, please read my article on Select statements. It will give you a basis for what we will cover next.

Select Statements and Unique Indexes

Select statements are very similar to ‘while select’ statements. But as stated before, they are almost always used to retrieve only one record. Choose to use a select statement when you know that only one record will be returned. The way to do that is to specify enough criteria in their ‘where clauses‘ and/or their joins to ensure that only one record will be found.

In order to know which type to use, look at the indexes on the table.

On each table, there is often a ‘unique index’. An index with the ‘Allow duplicates’ property set to ‘No’. This means that only one record can contain the same value for one or more fields.

For example, on all sales orders, there is a ‘SalesID‘ field. And by design, no two sales orders can have the exact same value in the SalesId field.

We can confirm this by looking at the table itself. Sales orders are stored in the table SalesTable. In Visual Studio, use the Application Explorer to find the SalesTable table. First, right click and select ‘open designer’. Second, expand the ‘Indexes’ node. Third, right click the index named ‘SalesIdx‘ and select Properties. Notice the property ‘Allow duplicates‘ is set to No. Finally, expand the ‘SalesIdx‘ node.

To summarize, the index only contains the field SalesId, and the Allow Duplicates property is set to ‘No‘. The same value cannot be stored in the field or fields listed in the index twice. Therefore, if we use this field in the ‘where’ clause of a ‘select statement’, we will only return one record.

Example Select Statement

The way to write x++ code to retrieve only one sales order with salesID “000001”, is this:

        SalesTable salesTable;
        select firstonly salesTable
            where salesTable.SalesId == "000001";

After this code runs, the system will have stored all of the fields on the one record into the table buffer variable named salesTable.

Note: If we had written the code “Select firstonly salesTable” without a ‘where’ clause the code will still run. But it will just retrieve the first record in the table. And not necessarily the record we are interested in using.

While Select

Similar to a select statement, x++ code is written to specify what records should be retrieved from the database. However, a while select statement should be used when the number of records being retrieved could be more than one.

Somewhat similar to a ‘while loop‘ or ‘for loop‘ in other languages, a ‘while select statement’ has a code block defined by a starting curly brace bracket, and a closing curly brace bracket. However, in a ‘while select’, each time the system starts a new loop, the next record in the results of the query is loaded in the table buffer variable.

Example While Select

For example, look at this code.

SalesTable salesTable;
while select salesTable
{
     Info(salesTable.SalesId);
}

I will explain each line of code carefully.

The system starts by declaring a table buffer variable named ‘salesTable’, which is of type ‘SalesTable’. We could have named the variable ‘mySalesTable’ instead of ‘salesTable’. But it is usually a best practice to name the table buffer variable the same as the name of the table, just with a lower case first letter.

In the next line, the system starts by looking at the query part of the select statement. In this case: ‘select salesTable’. This tells the system to retrieve every record in the table named SalesTable.

Next, at the start of the curly brace, the system will take all the values in the first record retrieved, and load them into the salesTable table buffer variable. After that, the ‘info(salesTable.salesId);’ line of code will print out the sales order number to the screen. Finally, the system will see the ending curly brace, and jump back to the starting curly brace.

When the system goes to the starting curly brace again, the next record retrieved by the query will be loaded into the table buffer variable salesTable.

See additional Microsoft documentation here.

When To Use While Select Statements

Use ‘while select’ statements to process records. For example, to process a sales order, each sales line needs to be looped through. And for each sales line, inventory journals need to be posted, and financials need to be written. Additionally, the status on each sales line needs to be updated.

There are many times when one action, such as updating a value, or pushing a button needs to read or update values on many records. This is where ‘while select’ statements are extremely useful. Data is retrieved, and loaded very quickly and easily. Allowing developers to focus on what they need to do with the data.

Furthermore, it is common to use ‘while select’ statements in Batch jobs. Additionally, in SysOperation framework jobs. As well as runnable classes. Processing a lot of records can take a long time. And it does not always make sense to have the user wait for the process to complete. Therefore, it is helpful to run the code in the background, and schedule it to run on a reoccurrence.

Look at this ‘while select’ statement used in the ‘run’ method of the MCRFTCEventProcessBatch class.

while select createdDateTime, SalesId from mcrOrderEventTableStart
where mcrOrderEventTableStart.MCROrderEventType == MCROrderEventType::FTCClockStarted
&& mcrOrderEventTableStart.createdDateTime >= DateTimeUtil::newDateTime(startDate, 0)
&& mcrOrderEventTableStart.createdDateTime <= DateTimeUtil::newDateTime(endDate, time)
&& (mcrOrderEventTableStart.createdDateTime <= firstDateTime
|| mcrOrderEventTableStart.createdDateTime <= secondDateTime
|| mcrOrderEventTableStart.createdDateTime <= cancelDateTime)
join salesTable
where salesTable.SalesId == mcrOrderEventTableStart.SalesId
&& salesTable.SalesStatus == SalesStatus::Backorder
notExists join mcrOrderEventTableStop
where mcrOrderEventTableStop.SalesId == mcrOrderEventTableStart.SalesId
&& (mcrOrderEventTableStop.MCROrderEventType == MCROrderEventType::FTCClockStopped
|| mcrOrderEventTableStop.MCROrderEventType == MCROrderEventType::FTCBackOrderCancellation)

{

}

This code will loop through all mcrOrderEventTable records that meet the specified criteria. And perform an action on each record.

This query is more complex that what I have shown you so far. I will cover join statements in another article.

Additional Examples

One of the best way to learn how to write ‘while select’ statements is to look at lots of examples. Consider this example of how to loop through all sales orders that have an ‘open order’ status.

        SalesTable salesTable;
        while select salesTable
            where salesTable.SalesStatus == SalesStatus::Backorder
        {
            Info(salesTable.SalesId);
        }

Similarly, consider this example, that loops through all sales orders for customer account “100001”.

        SalesTable salesTable;
        while select salesTable
            where salesTable.CustAccount == "100001"
        {
            Info(salesTable.SalesId);
        }

Common Misunderstanding

When people write ‘while select’ statements, they may be thinking they will load or see all of the records retrieved by the query at once. Consequently, this is a common misunderstanding for people. There is not a way to see all of the records returned.

To put it differently, there is not a ‘list’ that you can see in the debugger that will show you all of the records. Instead, each record of the result set it loaded into the table buffer variable one at a time.

This is great for the code inside the loop. However, it is hard to determine if the right data is returned.

Tools For Viewing The Records

There are a few ways to see the data retrieved.

First, you can use the ‘info’ method to print out information on each record. This is not always convenient, as you may need to look at many different fields on each record.

Secondly, you can set a breakpoint in-between the curly braces of a while select loop and use the debugger to view the values of the table buffer variable.

Thirdly, you can open SQL Server Management Studio, write a SQL statement that retrieves the same data as your x++ statement, and view the results. Although this option does take some extra work, it can be the best option for viewing all the data. Additionally, is is very helpful for troubleshooting queries that are not returning the data you expect. Many people may not know how to write a transaction-SQL select statement. However, learning how to can be a great help when writing x++ select and while select statements.

Once you know that your x++ statement is retrieving the correct records, you can focus on what you want to do with those records. And put that code inside the curly braces of ‘while select’ loop.

While Select Loops Within While Select Loops

Before completing this article, I wanted to call out one other common pitfall. Whenever possible, do NOT write ‘while select’ statements within other ‘while select’ statements. As a result, the performance of the system will be much slower.

To explain, let me show you an example. Consider you want to loop through all sales lines on every sales order that is in an ‘open order’ status. You might decide to write this:

SalesTable salesTable;
SalesLine salesLine;
while select salesTable
    where salesTable.SalesStatus == SalesStatus::Backorder
    {
        while select salesLine
            where salesLine.SalesId == salesTable.SalesId
        {
                Info(strFmt("SalesID: %1, LineNumber %2, ItemId %3",
                        salesLine.SalesId, salesLine.LineNum, salesLine.ItemId);
        }
    }

Ultimately, this code would work. But it would have bad performance.

Specifically, every time, through the first ‘while select’ loop the system is retrieving and loading the salesTable table buffer variable. Next, the system is going back to the database to retrieve and load each salesLine table buffer variable that meets the criteria.

The back and forth trips to the SQL server database slow the process down a lot. Additionally, the system has more SQL statements to compile and run. Consequently, this also slows things down.

In other words, you want to decrease the number of times the system has to run the code between the set of parenthesis. In this example, the system goes into one set of curly braces, after finding a ‘salesTable’. And then again, once the system finds a ‘salesLine’.

Combining Select Statements

Sometimes, there is no way around using a ‘while select ‘statement within another ‘while select’ statement. These cases usually occur when there is an ‘if’ statement that wraps the inner ‘while select’ statement. And the code within the ‘if’ statement calls another more complex method.

But in many other cases, like the one above, two ‘while select’ statements should be combined into one. By doing so, this can have a dramatic improvement on the performance of the code.

To demonstrate, look at this code.

        SalesTable  salesTable;
        SalesLine   salesLine;
        while select salesTable
            where salesTable.SalesStatus == SalesStatus::Backorder
            join salesLine
                where salesLine.SalesId == salesTable.SalesId
        {
            Info(strFmt("SalesID:  %1, LineNumber %2, ItemId %3",
                        salesLine.SalesId, salesLine.LineNum, salesLine.ItemId);
        }

In the above code, the two ‘while select’ statements are combined together using a ‘join’ statement. I will cover ‘join’ statements in another article.

As a developer, you may start out by writing two ‘while select’ statements. As this is how our minds work. However, you should then consider whether you can use a ‘join’ statement to combine the two statements into one.

In the old code, the system needed to enter into the curly braces, for every sales order, and for every sales line in each order. Whereas now, the system only needs to enter the curly braces for each sales line.

To emphasize this further, pretend you have 1000 open sales orders with 5 sales lines on each order. Using the old code, the system enters a set of curly braces 1,000 + (5,000) times. Or 6,000 times. Comparatively, using the new code, the system will enter a set of curly braces only 5,000 times. This is roughly a 20 percent improvement in performance.

Further Improvements

As can be seen, even when writing while select statements that retrieve the correct records, the way you write the statements is important. I won’t go into detail in this article for the sake of brevity and understand. However, consider the below code is even more optimized.

        SalesTable  salesTable;
        SalesLine   salesLine;
        while select SalesId, LineNum, ItemId from salesLine
                where salesLine.SalesId == salesTable.SalesId
            exists join salesTable
            where salesTable.SalesStatus == SalesStatus::Backorder
        {
            Info(strFmt("SalesID:  %1, LineNumber %2, ItemId %3",
                        salesLine.SalesId, salesLine.LineNum, salesLine.ItemId);
        }

This time, I am selecting ‘sales line’ first, and using an ‘exists join’ on the ‘salesTable’. These joins are beyond the scope of this article. But in brief, this tells the system to validate that a record does exist that meets the criteria. But it does not retrieve or load any values from the record. Improving performance even more.

Additionally, I am using a ‘field list’. Which means I am only selecting the fields on the salesLine record that I am actually using.

Conclusion

Knowing how to write a while select statement is extremely useful. This syntax allows developers to retrieve and run high level code on specific lists of records. However, developers needs to be careful. While select statements can have the system process a lot of records. Even when the correct records are retrieved, do not think you are finished. The system can retrieve these records faster when the queries are written in the best possible way. Therefore, it is very important to review these statements and make sure to write them the most efficient way.