X++ Standards: select Statements
The basic rules for select statements are as follows:
Specify what records you need by using the where statement to limit the returned rows. To sort the returned records, use the order by statement.
Add an index hint on realistic data only if you discover performance problems without the hint.
Use the index keyword if the order should be defined by the index definition.
Most select statements should be written without an index or an index hint, leaving the job of ordering the data to the optimizer in the database system. Whenever you use the index hint functionality, make a comment about why you are explicitly specifying it, thereby taking control away from the database management system (DBMS). Consider using the forceLiterals or forcePlaceholder statements as well or instead of select statements.
Be careful when using the forceLiterals keyword in X++ select statements. It could expose code to an SQL injection security threat.
Use the order by statement when you want the data ordered, and you do not want to specify which index to use. Ordering of data can take time and should only be done if needed.
Use the index keyword when you want the data to be centrally ordered as specified in the index specification on the table. If the index is specified as Enabled: No, an index is not generated by the database system.
If you are going to use only the first record or if only one record can be found, use the firstOnly qualifier. This optimizes the select statement for only one record.
The firstOnly qualifier does not guarantee that only one record is returned.
Do not use while select firstOnly.
The firstOnly qualifier and the field list are implicit in (select...).<field> statements, and are not explicitly needed. Use this kind of select statement wherever reasonable.
If a select statement is local to a method, use a field list to increase performance. If you use a select or a while select statement and the size of the fields that are used total less than 50 percent of the total record size, a warning appears if you do not use a field list.
If a select statement is local to a method and the firstOnly qualifier is not used, you must use the next statement on the result set.
Here are two (contrived) examples of how to find ledger transactions in account number, transaction date order.
The following code shows how to find the last week's (that is, a few days) transactions on all of the (many) Profit and Loss accounts.
select firstOnly ledgerTrans
index hint DateIdx
order by accountNum, transDate
where ledgerTrans.accountNum >= '40000'
&& ledgerTrans.accountNum <= '99999'
&& ledgerTrans.transDate >= 26\04\1999
&& ledgerTrans.transDate <= 02\05\1999;
The following code shows how to find transactions for the entire year (many dates) on (the few) liquid assets accounts.
while select ledgerTrans
order by accountNum, transDate
where ledgerTrans.accountNum >= '11100'
&& ledgerTrans.accountNum <= '11190'
&& ledgerTrans.transDate >= 01\07\1999
&& ledgerTrans.transDate <= 30\06\2000
// Do whatever is needed.
Following are best practices from the previous examples:
The index, order by, and where statements are indented once relative to the select or while select statements.
The where expression is structured in a column.
The Boolean operators (&&) are at the beginning of the line (and in columns).
The while select block has braces even though it contains only one statement.
The braces are at the same column position as the while block.
The uppercase- and lowercase-name standards are adhered to.