Use X++ to write SQL statements

5 minutes

You can use SQL statements in X++ to manipulate data by using select, insert_recordset, update_recordset, and delete keywords.

Select statements are used to choose what data will be retrieved from the database. In a select statement, you can choose which tables you are pulling from, choose which fields that you want to retrieve from those tables, set conditions to further filter the data, and select the order in which the data is listed. The select statement contains the keyword "select" and parameters. Parameters contain the different pieces of the select statement that determine how and what data is pulled.

The following is an example of the select statement syntax:

Select [FindOptions] [FieldList] from [Table] [Options] [OrderByClause] [WhereClause] [JoinClause]

The Find options parameters give additional options for pulling data. For example, crossCompany pulls data across legal entities, firstOnly pulls 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. If you use the asterisk (*) in the Field list, it will pull all fields for a given record, but we do not recommend that procedure.

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

The join clause can be used to join additional tables to a select statement. You can use relational operators in where clauses in select statements. The statement will fetch all records where this expression yields true. The minimum requirements for a select statement are the keyword "select," the keyword "from," and the table.

The following is an example of a select statement. The first statement defines the field to pull and the second statement uses the asterisk. We recommend that you define the fields to pull in when writing a select statement.

	Select AccountNum from CustTable;
		
	Select * from CustTable;

You can add other parameters to make more complex select statements. In this example, doing so pulls all the records from CustTable where the AccountNum values are greater than 1000 and less than 2000. The pulled 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';

The following is an example of how a join statement might look. The example shows the joining of the SalesLine record to the related SalesTable where the sales ID on the sales line matches the sales table sales ID.

Select * from SalesLine
join SalesTable where SalesLine.SalesId == SalesTable.SalesId;

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. In the following example, instead of pulling the first record that matches the criteria, the while select statement will loop through each record where the criterion is true. When each record is selected, it will then run through the additional logic that is inserted between the brackets.

While select forUpdate * from CustTable 
	order by AccountNum desc
	where custTable.accountNum > '1000'
	&& custTable.accountNum < '2000'
{
	(Additional logic here.)
}

Insert methods can be used to add a new record to a table. Insert methods will 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.

The following is an example of using an insert method and the Insert_recordset statement:

ttsbegin;
salesLine.unitPrice = 4;
salesline.insert();
ttscommit;

insert_recordSet myTable (myNum)
	select myNum
		from anotherTable;

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.

ttsbegin;
Select forupdate salesLine where salesline.salesId == 'S0001';
salesLine.unitPrice = 10;
salesLine.update();
ttsCommit;
 		
update_recordset salesLine
setting unitPrice = 10
where salesId =='S0001';

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 at once with the delete_from statement.

The following is an example of using a delete method and the delete_from statement.

ttsbegin;
Select forupdate salesLine where salesline.salesId == 'S0001';
salesLine.delete();
ttsCommit;
			
delete_from salesLine
where salesLine.salesId == 'S0001';

You can use this information as the building blocks to creating more complex queries.

Continue

Feedback