D365 Snippets & Power BI

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);
      }
    }
}

In the previous article, I explained what is x++ select statements and steps for writing An X++ Select Statement in Dynamics 365 finance and operations. In this article, i am writing the continuation of the x++ select statement with a where clause is used with select statement.

A select statement allows developers to fetch and manipulate data from a database. The data is loaded into table variables or table buffers. Finally, these table buffers 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. 

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.

Select Statement Where Clause

internal final class SelectQueryExample
{

   public static void main(Args _args)
   {
      CustTable custTable;
      select * from custTable where custTable.AccountNum == “CUS10001”;
      info("AccountNum: " + custTable.AccountNum);
   }
 
}

In this example: 

• select * from custTable: This part of the statement selects all fields from the CustTable table. 

• where custTable.AccountNum == 'CUS10001': This part filters the records to only include those where the AccountNum field is equal to 'CUS10001'.

You can use various comparison operators in the 'where' clause, such as == (equal), != (not equal), < (less than), > (greater than), <= (less than or equal to), and >= (greater than or equal to). 

Additionally, you can use logical operators (&& for AND, || for OR) to combine multiple conditions in the 'where' clause.

In our example a 'where' clause essentially used to return the record we want, the AccountNum field is the primary field of the unique index on this table. which means AccountNum field should not be duplicated and should be unique. So when we filter the account number with the required value which will return only one record that we need actually.

But if we are not using the 'where' clause the system will store the table buffer with the first record which find.

The major difference of x++ select query with where clause to the normal sql query is ,first difference is, in normal sql queries we use '=' one equal to sign, but with x++ we use two equal to signs '==', Second difference is a table buffer variable must be used with x++ select queries.

In conclusion, without the ‘where’ cause the system would just be picking the first record it finds. which is not very helpful. Now, when werun the above code, the system will select the record where the AccountNum equal to the account number we pass, which acts as a filter and returns the value what we exactly want. As a beginner this topic will help you to learn more about x++ select statements , in the next article we will learn 'what is a field list and what is the use of field list and how to write a x++ statement with field list'.