X++ While Select Statement in Dynamics 365 Finance and Operations

In Dynamics 365 Finance and Operations, the ‘select’ statement is commonly used to retrieve data from tables in the application's database. A select statement allows developers to fetch and manipulate data from a database. The data is loaded into table variables. Finally, these table variables can have methods where code be added to work with the data. Unlike other programming languages, writing select statements using x++ select statement is a very fast and easy.

Read More : X++ Select Statements

Read More  : X++ Select Statement with Where Clause

In Microsoft dynamics 365 finance and operations, while creating a table, system automatically allows the user to use the table variables or table buffers with a set of table properties including field names. Therefore, there is no need for a developer to manually create a class to work with the data in this table. The system essentially has created one for your use already.

Difference between While select and Select Statement

In the previous article we already read that a select statement can be used or only can return one record and store the results in the table buffer. But in many situations, we need to retrieve many records on a single select statement, in these cases we need to use a while statement, a ‘while select’ statements are used to find one or more records.

Here you can see one simple select query example which select the first record of VendTable and store into the table buffer vendTable.

internal final class SelectQueryExample
{ 
   public static void main(Args _args)
   {
       VendTable   vendTable;
       select * from vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
       select vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
   }
}

Above both select queries retrieve one record as shown in the below screenshot.

X++ Next Keyword

We have a requirement to retrieve more than one vendor account number, Microsoft introduced a new keyword called ‘Next ‘Keyword. Which we can use multiple records to retrieve from a table buffer,

Syntax of Next keyword,

You can see the below snippet example to understand the use of the Next keyword, 

internal final class SelectQueryExample
{ 
   public static void main(Args _args)
   {
       VendTable   vendTable;
       select * from vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
       select vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
       next vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
       next vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
       next vendTable;
       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
   }
}

Here we need to write the separate lines of codes to retrieve the multiple number of vendor Account Number from a single table buffer. So, if we need to retrieve ‘n’ number of Vendor Account Number, we need to write ‘n’ number of lines by using next keyword.

But this is not practical, if we have thousands of records, we are not able to write thousands of lines of codes with next keyword. 

In these cases, we are using while statement.

While Select Statement

In Dynamics 365 Finance and Operations, the "while select" statement is commonly used in X++ programming language to iterate through records in a table or a view and execute a block of code if a specified condition is true. This statement is often used for data manipulation and processing.
Like 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.

internal final class SelectQueryExample
{ 
   public static void main(Args _args)
   {
       VendTable   vendTable;
       while select vendTable;
         {
            info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));
         }
    }
}

In this example you can see, we have declared one table buffer vendTable, In the next line, the system starts by looking at the query part of the select statement. Here before the select statement we have written the keyword ‘while’.

In this case: ‘while select vendTable. This tells the system to retrieve every record in the table named VendTable. 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 vendTable table buffer variable. After that, the ‘info(strFmt("Vendor Account Number = %1", vendTable.AccountNum));’ 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.

While select statement with where clause

In Dynamics 365 Finance and Operations, the "while select" statement is commonly used in X++ programming language to iterate through records in a table or a view and execute a block of code if a specified condition is true. This statement is often used for data manipulation and processing.

You can use a where clause with while statement , which will retrieve the records only if a specified condition is true. If the specific condition is false, system will not retrieve any records. While select statement with where clause is generally used to retrieve multiple records which are satisfying a specific condition.

internal final class SelectQueryExample
{ 
   public static void main(Args _args)
   {    
     while select AccountNum, Name, Balance from CustTable
     where CustTable.Balance > 1000
      {
          info("Account Number: " + CustTable.AccountNum);   
          info("Customer Name: " + CustTable.Name);   
          info("Balance: " + CustTable.Balance);
      }
    }
}