Using Select statement in X++ to retrieve data

X++ uses select statements to fetch data from the database. And the advantage of X++ is that Select statements can be written from code like any other statements.

Below is the Happy path to use Select statements in your code:

1.      Declare variable: In order to use a select statement you must first declare variables for the tables being referenced

2.      While select: A special form of the while statement, while select, can be used to create a loop that will fetch all of the records that fulfill specific criteria

3.      Selection criteria: The selection criteria are defined using expressions based on operators and variables. Just prepare a criteria based on your requirements and while select will take care of rest.

Example (1):

static void Intro_SelectStmt(Args _args)

{

CustTable custTable;

custTrans custTrans;

;

setprefix("Using select statements in X++: ");

while select * from custTrans where custTrans.Approved == false

{

setprefix("CustTrans records:");

info(strfmt("%1, %2", custTrans.AccountNum, custTrans.AmountMST));

}

while select * from custTable where custTable.AccountNum > "4000" && custTable.AccountNum < "4005"

{

setprefix("CustTable records");

info(strfmt("%1, %2, %3", custTable.accountNum, custTable.Address, custTable.rowNumber));

}

}

Output:

           

Tip:

Notice that the base type str cannot be used in expressions, unless the length of str is specified. This is yet another reason to use extended data types instead of base data types.

Reference Links:
More examples here -- http://msdn.microsoft.com/en-us/library/aa848113(v=ax.50).aspx