---

This topic describes the support for data selection and manipulation in the X++ language.

You can use SQL statements, either interactively or within source code, to access and retrieve data that is stored in the database. You use the following statements for data manipulation:

• select – Select the data to modify.
• insert – Add one or more new records to a table.
• update – Modify data in existing table records.
• delete – Remove existing records from a table.

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)); 
    }

}

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));     
}

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));     
    }
}

---