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);
}
}
}
- while select: Initiates the loop.
- AccountNum, Name, Balance: Specifies the fields to be selected from the "CustTable."
- from CustTable: Specifies the table from which records are selected.
- where CustTable.Balance > 1000: Sets the condition that only records with a balance greater than 1000 will be selected.
- { //code to be executed for each selected record }: The block of code inside the curly braces will be executed for each record that meets the specified condition.This example selects records from the "CustTable" where the balance is greater than 1000 and displays the account number, customer name, and balance for each selected record.Using the "while select" statement with a "where" clause allows you to process a subset of records based on specific conditions, providing flexibility in handling data in Dynamics 365 Finance and Operations.
Conclusion
After reading this article, i expect you can get the basic idea about 'x++ select statement' and 'x++ while select statement', for writng efficient codes to retrive many records in a single statement with a condition or without a condition , you can use while select statement. if this article is useful, try to share your friends and social media platform. Happy coding with d365snippets.
