X++ Select Statement with Where Clause in Dynamics 365 Finance and Operations

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

A select statement without where clause only return one record and store the results in the table buffer, The 'where' clause in the select statement is used to filter the records returned by the query based on specified conditions. To do that, we will add a ‘where’ clause to our select statement. Change the code to look like this:

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'.