Select statement in X++

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.

Before any data can be changed, you must use a select statement to select the data to update. The select forUpdate command selects records for update only. The insert, update, and delete statements perform operations on one record at a time. The array insert, insert_recordset, RecordInsertList, and update_recordset statements perform operations on multiple records at the same time.

X++ code: Select data from table: Show first row

This code will select only first row in the table.

// we have a table: Table name is <Table1>
// table contain two columns: 1. SO_ID 2. status
// we want to select the above table 
// this will show the first row values

static void RummanJob1(Args _args)
{
      
        Table1 myTable;         
        select * from myTable;
        info(strFmt("%1 %2", myTable.SO_ID, myTable.status)); 

}

X++ code: Select data from table: Show all rows

// we have a table: Table name is <Table1>
// table contain two columns: 1. SO_ID 2. status
// we want to select the above table 
// this will show the all row values

static void RummanJob1(Args _args)
{
      
    Table1 myTable;         
        
    while select * from myTable
    {
        info(strFmt("%1 %2", myTable.SO_ID, myTable.status)); 
    }

}

X++ code: Select data from table: Select few specific column

// we have a table: Table name is <Table1>
// table contains two columns: 1. SO_ID 2. status
// we want to select the above table 
// this will show the all row values
// select a single column named status

static void RummanJob1(Args _args)
{
      
    Table1 myTable;         
        
    while select status from myTable
    {
        info(strFmt("Value of status is : %1",  myTable.status)); 
    }

}

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

	Select AccountNum from custtable;

X++ code: Select data from table: Select rows using where clause

// we have a table: Table name is <Table1>
// table contains two columns: 1. SO_ID 2. status
// we want to select the above table 
// select using where clause

static void RummanJob1(Args _args)
{  
        Table1 myTable;  
        select * from myTable where myTable.status = "good";
        info(strfmt("%1 %2", myTable.SO_ID, myTable.status));     
}

X++ Code: Select Data from Table with Where Clause and Sorting

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

firstOnly, firstOnly10, firstOnly100, and firstOnly1000 keywords

The firstOnly keywords speed up the fetch by returning a limited number of rows. When you include firstOnly in your query, the runtime returns a table buffer. When you omit firstOnly, the runtime allocates an object that can iterate over records. From a performance perspective, you should use firstOnly only when your intent is to fetch the first record.

X++ code: Select data from table: firstOnly

// we have a table: Table name is <Table1>
// table contains two columns: 1. SO_ID 2. status
// we want to select the above table 
// select using firstOnly 

static void RummanJob1(Args _args)
{  
        Table1 myTable;  
        select firstOnly myTable{  // this is a short notation for 'select firstonly * from myTable;'  
        info(strfmt("%1 %2", myTable.SO_ID, myTable.status));     
    }
}

X++ code: Select data from table: firstOnly10

// we have a table: Table name is <Table1>
// table contains two columns: 1. SO_ID 2. status
// we want to select the above table 
// select using firstOnly10 

static void RummanJob1(Args _args)
{  
        Table1 myTable;  
        while select firstOnly10 myTable{
        info(strfmt("%1 %2", myTable.SO_ID, myTable.status));     
    }
}

Another options for you

firstOnly1

firstOnly10

firstOnly100

firstOnly1000

X++ code: Select data from table: reverse

// we have a table: Table name is <Table1>
// table contains two columns: 1. SO_ID 2. status
// we want to select the above table 
// select using reverse 

static void RummanJob1(Args _args)
{  
        Table1 myTable;  
        while select reverse myTable{
        info(strfmt("%1 %2", myTable.SO_ID, myTable.status));     
    }
}

X++ code: Select data from table: firstfast

FirstFast instructs the SQL-database to prioritize fetching the first few rows fast over fetching the complete result set. This also means that the SQL-database might select an index fitting the order by clause over an index fitting the "where" clause. The FirstFast hint is automatically issued from all forms, but is rarely used directly from X++.

// we have a table: Table name is <Table1>
// table contains two columns: 1. SO_ID 2. status
// we want to select the above table 
// select using firstfast 

static void RummanJob1(Args _args)
{  
        Table1 myTable;  
        while select firstfast myTable{
        info(strfmt("%1 %2", myTable.SO_ID, myTable.status));     
    }
}