Use X++ to write SQL statements

5 minutes

You can use SQL statements in X++ to retrieve and manipulate data in the Finance and operations database.

Select statements

You can use select statements to choose what data is retrieved from the database. In a select statement, you must define the table you retrieve data from and which field. You can use conditions to further filter the data, and select the sequence in which the data is listed.

The select statement contains the keyword select and parameters. Parameters contain the pieces of the select statement that determine how and what data is retrieved.

The following is an example of the select statement syntax:

Select [FindOptions] [FieldList] from [Table] [Options] [OrderByClause] [WhereClause] [JoinClause] method()

The Find options parameters give additional options for retrieving data. For example, crossCompany retrieves data across legal entities, firstOnly retrieves only the first record, firstOnly10 only fetches the first ten records, and forUpdate selects the data with a lock.

The Field list specifies which data to retrieve. You can also use aggregate functions, like sum, avg, minof, maxof or count.

After the Field list, the keyword "from" is used, followed by the table to retrieve from. You can then include options to sequence or group the data by using "order by" and "group by" keywords. The where clause uses expressions to define the criteria for the statement.

Here's an example of a select statement. This statement retrieves all account numbers from the CustTable.

	Select AccountNum from custtable;

You can add parameters to make more complex select statements. In this example, doing so retrieves all the records from CustTable where the AccountNum values are greater than 1000 and less than 2000. The records are also sorted by the AccountNum values in descending order.

Select forUpdate  CustTable order by AccountNum desc
	where custTable.accountNum > '1000'
	&& custTable.accountNum < '2000';

Join methods

A join can be used to combine more tables to a select statement. You can use relational operators such as equal to, not equal to, greater than, less than in where clauses in select statements.

The following is an example of a join statement. The statement joins a SalesLine record to the related SalesTable where the sales ID on the sales line matches the sales table sales ID.

A select statement returns all records, and remaining records can be fetched by using a next statement.

A while select statement loops through every record that matches the select criteria, which is common for data manipulation.

SalesTable SalesTable;
SalesLine SalesLine;

// Select with join (first query)
select SalesLine
     join SalesTable
     where SalesLine.SalesId == SalesTable.SalesId;

{
     // Process the results of the select
     info(strFmt("SalesId: %1, ItemId: %2, CustAccount: %3",
SalesLine.SalesId, SalesLine.ItemId, SalesTable.CustAccount));
}
// While select with join (second query)
while select SalesLine
     join SalesTable
     where SalesLine.SalesId == SalesTable.SalesId

{ 
// Process each record in the while loop 
Info(strFmt("Processing SalesId: %1, ItemId: %2, CustAccount: %3",
salesLine.SalesId, salesLine.ItemId, salesTable.CustAccount));
}

In the following example, instead of retrieving the first record that matches the criteria, the while select statement loops through each record where the criterion is true. In the code below, all account numbers that begin with US are retrieved, and the CreditMax for each record is updated to 5000.

{
    CustTable custTable;

while select forUpdate custTable
      where custTable.AccountNum like 'US*'
      {
      custTable.CreditMax = 5000;
      custTable.update();

Info(strFmt("Account number %1 has been updated", custTable.AccountNum));
      }
}

Insert methods

Insert methods can be used to add a new record to a table. Insert methods only insert the columns that are selected by the query. You can override the insert method's validations and processes by using the doInsert() method. The insert_recordset statement can insert multiple records by copying the records from one table and inserting them into another.

Insert methods need ttsBegin and ttsCommit to ensure that the data insertion process is handled as a single transaction.

The following is an example of using an insert method statement to create a new customer.

{
FMCustomer fmCustomer;

ttsBegin;  
fmCustomer.FirstName = "John";
fmCustomer.LastName = "Doe";
fmCustomer.Email = "JohnDoe@microsoft.com";
fmCustomer.CellPhone = "555-555-5555";
fmCustomer.insert();
ttsCommit;

Info("New customer has been created.");
}

Update methods

Update methods can change existing values on a table record. To update a record, you must use a select forUpdate command to indicate that the records are for update. Update methods also need to be wrapped in a ttsbegin and ttscommit statement. You can also use the update_recordset to update multiple records at the same time.

The following is an example of using an update method and the update_recordset statement.

{
    FMCustomer fmcustomer;
    select forUpdate fmcustomer
    where fmcustomer.firstname == 'John';
    ttsbegin;
    fmcustomer.firstname = 'Johnny';
    fmcustomer.update();
    ttscommit;

    Info("Record has been updated");
}

Delete methods

Delete methods can remove an existing record from a table. You can use the doDelete() method to override the standard delete method behavior. You can also delete multiple records simultaneously with the delete_from statement. Delete Methods also need to be wrapped in a ttsBegin and ttsCommit statement, where you delete the data.

The following is an example of using a delete method statement to delete a customer.

{
     FMCustomer fmcustomer;
     select forUpdate fmcustomer
     where fmcustomer.firstname == 'Johnny';
     ttsbegin;
     fmcustomer.delete();
     ttscommit;
     info("Record has been removed");
 }