Use X++ to write SQL statements
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");
}
You can use this information as the building blocks to create more complex queries.