How To Write An X++ Select Statement

One of the coolest parts of the x++ language is using a select statement. 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. This allows developers to focus on what they functionally need to do with the data. And not spend a lot of time on simply getting the data in the right place.

The Benefits of An X++ Select Statement

In many other languages, it takes a lot more work and code to get the data in a place where you can manipulate it. First, a class needs to be created, with class variables for each field in a table. Next, the data is retrieved using a sql statement. Then, code must be written to map the results to each property in the class object. Only then can a developer work with the data using a high level language. This is a lot of time consuming steps.

In contrast, using a x++ select statement is a very fast and easy. Whenever a developer creates a table in D365 or AX, the system automatically allows a developer to use a ‘table variable’ in the x++ code. This ‘table variable’, or ‘table buffer’ as they are often called, is essentially a class object with a property for each field on the table. 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.

Additionally, in other languages you need to write one sql statement to retrieve the data. Then, write more code to take the results and put the data in the table buffer. Whereas, the x++ select statement does this all in one step. Let us look at an example.

Example Select Statement

The easiest way to learn how to use a select statement is to start with an example. I can then explain how it works. Consider this example. This is also shown in the Microsoft documentation here.

CustTable custTable;
select * from custTable;
info("AccountNum: " + custTable.AccountNum);

To follow along yourself, create a new D365 project and solution in Visual Studio.

Next, add a ‘Runnable class (Job)’ to the solution. Specifically, right click on the project, and select Add>New Item.

Then, select the ‘Runnable Class (Job)’ item. Enter a name, such as ‘SelectStatement’. Finally, click the ‘Add’ button.

Copy the code into the ‘main’ method in the code editor window. The final result should look like this.

In the next three sections I will explain the code line by line.

Define a Table Buffer

In the first line, a table buffer of type ‘CustTable’ is defined, with the name ‘custTable’.

CustTable custTable;

The first word is the ‘type’ of table variable it is. And the second word is the name of the variable.

To better understand, you may have written code like this before:

string myString;

In the above code, the word ‘string’ is the type of variable. And the word ‘myString’ is the name of the variable. The same is true with a table buffer. The type ‘CustTable’ refers to the table that is defined within the Application Explorer. You can see this table by searching the Application Explorer for ‘CustTable’ and finding the node.

You can replace this first word with any table in the entire system. Including the name of any table that you have written. Pretty neat?

The second word is the name of the variable. It is a best practice to name the variable the same as the name of the table, when possible. Except, all variables should start with a lower case first letter. This helps developers know what Type of table variable it is without having to look at where it was defined.

Basic Select Statement

After defining a table variable, or table buffer as they are commonly called, the next step is to retrieve data. Right now, the table buffer is empty. There is no data in it. A Select statement is how we put data into the table buffer.

For example, the next line of the example we looked at is this:

select * from custTable;

First, the word ‘select’ tells the system we want to retrieve data from the database. Secondly, the character ‘*’ tells the system we want to select all fields from the table. Finally the last two words ‘from custTable’, tells the system the table buffer that should be populated with the data.

Note: If you are familiar with writing a transaction-SQL select statement, the syntax is similar. However, there are some important differences.

In transaction-SQL, the name of the table to read from is used. Whereas, in an x++ select statement the name of the table buffer variable is used. Specifically, notice that we used ‘custTable’ with the lower case first letter. To be very clear, if we had named the table buffer variable ‘myCustTable’ we would need to write the code like this:

select * from myCustTable;

The system then looks at the Type of table buffer variable being used, and therefore knows what table to pull from.

Ultimately, when this code is run, the system will query the database, retrieve the data, and store all of the results in the table buffer named ‘custTable’.

Using A Table Buffer

In the first two steps, we defined a table buffer variable and used a select statement to put data into it. Finally, in the last step, a developer can use the data in a table buffer bariable.

If this is the first time you are using a table buffer, you may not be too impressed yet. But you are about to see the value of a table buffer and a select statement.

On the last line of code, delete the word ‘AccountNum’ and the period before it, and then type the period again. Visual Studio will show all of the properties and methods on the table buffer. (This is called IntelliSense).

If you do not see the drop down, delete the period, and enter it again. Then wait a few seconds. As long as you do not have a compile error earlier in the code, you should see the drop down list.

Every field on the table will be shown. Additionally, every method defined on the table will be shown. Including methods like ‘insert‘, ‘update‘, and ‘delete‘, which are inherited by every table buffer variable.

To emphasize, without having to write a class with all of these variables, the system has done this for us. A developer simply needs to create a table, and the system allows a table buffer variable of that type to be used in the x++ code.

After the period, enter ‘AccountNum’. This is the name of a field on the table CustTable. And therefore, we can use that same name on the table buffer variable.

info("AccountNum: " + custTable.AccountNum);

The code ‘custTable.AccountNum’ will return the data stored in the field named ‘AccountNum’ on the record retrieved.

Finally, the info() method will show whatever text is passed into it to the screen.

Select Statement Where Clause

A select statement will only select one record, and store the results in the table buffer. Therefore, we need to make sure our code selects the one record we want, out of the many records in a table. To do that, we will add a ‘where’ clause to our select statement. Change the code to look like this:

CustTable custTable;
select * from custTable
where custTable.AccountNum == “100001”;
info(“AccountNum: ” + custTable.AccountNum);

To explain, a ‘where’ clause is essentially a filter that we use to reduce the data to return the records we want. In this case, the AccountNum field is the primary field of the unique index on this table. Which means each and every record in this table has a different value stored in the AccountNum. To put differently, no two records have the same value. This means that when we use a ‘where’ clause with AccountNum, we will only get one record back. And this is what we want.

Without this ‘where’ cause the system would just be picking the first record it finds, which is not very helpful. Now, when you run the above code, the system will select the record where the AccountNum is set to “100001”, assuming it exists. You can then use the table buffer properties to view other fields on this record.

Differences From Transaction-SQL

If you are used to using transaction-SQL there are a few differences I wanted to point out.

First, the table buffer variable name needs to be used in every where clause. You cannot just write ‘”AccountNum = ‘100001’” like you would in SQL. You instead write:

where custTable.AccountNum == "100001"

Secondly, in the where clause, you need to use two equal signs instead of one. You will get a compile error if you only use one equal sign, like in SQL.

Thirdly, it is a best practice to use double quotes when referencing a string value in x++. Whereas in SQL, you would always use single quotes.

Best Practices

Please note, the ‘where’ clause is usually written on a second line and indented from the first. The semi-colon that is at the end of every line of code in x++ should be added after the last line of the select statement.

Select statements can have many lines to them. The system treats them all as a single instruction in code. However, as a best practice, each part of a select statement should be put on its own line, and indented.

This makes the code easier to read. Additionally, it makes it so developers do not have to scroll to the right in a code editor window to see the code.

Field List

After writing a basic select statement in the x++ language, there are a few additional best practices to be aware of. The first best practice is using what is called a ‘field list’.

Instead of typing the character ‘*’ and retrieving and loading all of the fields in a table, you can use a ‘field list’ to specify a list of only the fields you need.

Instead of the lines of code we have used so far, you could write the below code.

CustTable custTable;
select AccountNum, CustGroup from custTable
where custTable.AccountNum == “100001”;
info(“AccountNum: ” + custTable.AccountNum);
info(“CustGroup: ” + custTable.CustGroup);

In the above code, “select AccountNum, CustGroup from custTable” tells the system to only retrieve the data in these two fields.

Why is this helpful? The primary reason a field list should be used is because it improves performance. Instead of the system having to read lots of data from all of the fields on the data, and load them into the table buffer, the system only needs to return the fields you specify.

In this example, the system only needs to return the data stored in two fields. Whereas, when you use “Select * from custTable” , the system has to return ALL of the fields in the table. And there are roughly 200 fields on the CustTable.

When To Use A Field List

That said, do not always use a field list. There are many times that the code will select a table buffer and then pass it into another method. In this case, you do not necessarily know what fields the calling method will need. So it is preferred you select every field to be safe.

Therefore, only use a field list when you know exactly the scope of how the table buffer will be used. Additionally, be sure to include all of the fields needed by the code in the field list. A common mistake is to think a field has no data, when in reality, you forgot to include that field in the field list.

Two Ways to Select All Fields

There are actually two ways of selecting all fields from a table. As you have seen so far, you can write:

select * from custTable;

The * tells the system to retrieve every field, without the user needing to list out every one. And then constantly update this list when new fields are added. Similar syntax is used in transaction-SQL.

That said, there is a second way of writing the same thing. It looks like this:

select custTable;

First, the developer types the word ‘select’. Then, they specify the name of the table buffer. That is it. The system understands that if a field list was not specified, this means that every field should be retrieved and loaded into the table buffer variable.

Firstonly

There are a lot of additional keywords that you can use in an x++ select statement that I will not cover in this article. But there is one that I did want to cover. It is the keyword ‘firstonly‘.

The keyword ‘firstonly‘ tells the system to only retrieve the first record it finds that meets the criteria of the statement, then stop.

Consider the following updated code:

        CustTable custTable;
        select firstonly custTable
            where custTable.AccountNum == "100001";
        info("AccountNum: " + custTable.AccountNum);
        info("CustGroup: " + custTable.CustGroup);

Notice the line “select firstonly custTable”. The code tells the system to look in the table named CustTable for the first record it can find where the AccountNum field contains the data “100001”. Normally the system would search for ALL of the records that meet the criteria. But by adding the firstonly keyword, we can improve the performance, by telling the system to stop after finding ONE.

This is very powerful because most of the time a developer uses a ‘select statement’, the code is written to only find one record. Developers will make sure they specify enough criteria in their ‘where’ clause or joins to only return one record.

There are other times where a developer needs to loop through and process multiple records. In those cases, a developer will use a ‘while select‘ statement. I will cover this in a separate article.

Running The Code

Push the play button in Visual Studio to run the Runnable Class (Job). The system will display the the values of the two fields to the screen in info messages.

Advanced Topics

So far, I have shown you the basics of how to write a select statement in x++. There is a whole lot more to learn about select statements. I wanted to very briefly mention some of the other things you can do.

Similar to transaction-SQL you can use to join to other tables. You can order and group by records. You can count and aggregate data in fields on tables.

To see some other examples please take a look at this Microsoft documentation.

I will also plan on covering some of these topics in future articles.

Summary

In this article you learned how to write an x++ select statement. I explained the basic syntax. Furthermore, I explained how select statements load data into table buffer variables. And the table buffer variables can have methods with high level code to make working with the data so much easier.

X++ select statements are similar in syntax to transaction-SQL. But there are quite a few differences. So my hope is that this article helped explain some of the differences. And allow you to use this very powerful part of the x++ language.