Select statement patterns

This article contains descriptions of select statement patterns that can be used in the Microsoft Dynamics AX (Axapta).
The list of select statement patterns provided is as follows:

1. Enable/Disable condition
2. Switch condition
3. Enable/Disable join table with condition

Enable/Disable Condition

Use this pattern when the condition in the “where” clause is dependant of a parameter.
For example, we need to perform some procedure with either all Customer or Customers with invoice account only. It depends on the User input parameter. We create a periodic job with the following dialog box:

Process Customers

We will assume that the Customer with invoice account check box has the dialogField name in the code.
The select statement will have the following view in the “run” method:

while select custTable

where (!dialogField.value() || custTable.InvoiceAccount != '')

If the check box is selected, !dialogField.value() returns false and custTable.InvoiceAccount != ” will be taken into account. If the check box isn’t selected, !dialogField.value() returns true (so there is no sense what return the custTable.InvoiceAccount != ” condition). In other words, if the check box is selected, only Customers with Invoice accounts will be searched. Otherwise, all Customers will be searched.
General structure looks as follows: where (!EnableConditionFlag || condition)

Switch Condition

Use this pattern when either one or another condition dependant of parameter must be applied to the select statement.
For example, we need to perform a procedure with items of either a BOM item or items of other item types. We need to create a periodic job with a dialog box. The dialog box contains only one BOM type checkbox. We will assume that the BOM type check box has the dialogField name in the code.
In the run method, we write the following select statement:

while select inventTable

where ((!dialogField.value() && inventTable.ItemType != ItemType::BOM) ||

       (dialogField.value()  && inventTable.ItemType == ItemType::BOM))

If the BOM type check box is selected, the first parenthesis returns false and everything depends on the second parenthesis where the inventTable.ItemType == ItemType::BOM clause is checked. If the BOM type check box isn’t selected, the second parenthesis returns false and everything depends on the first parenthesis where the inventTable.ItemType != ItemType::BOM clause is checked. In other words, if the check box is selected, the only BOM item will be searched. Otherwise, only not BOM items will be searched.
General structure looks as follows: where ((!Condition2Flag && condition 1) || (Condition2Flag && condition 2))

Enable/Disable Join Table with Condition

Use this pattern when a joined table with a condition can either contain records or be empty.
For example, we need select sales orders without Recipient or terminated Recipient. The SalesTaker field in the SalesTable table stores the Recipient ID. The Status field in the EmplTable table stores the Recipient’s status (None, Employed, or Resigned).
The select statement has the following view:

while select SalesTable

notexists join EmplTable

where EmplTable.EmplId == SalesTable.SalesTaker &&

      EmplTable.status != HRMEmplStatus::Resigned

If EmplTable.status is Resigned or the Sales table record doesn’t have the Recipient, the Sales table record will be selected.
General structure looks as follows: notexists join <TableName> where <relation clause> && (condition != required value)
If you know other interesting patterns, please write them in the comments


Copied from Experts website....... Dynamicsaxtraining.
You can always reach me at axapta4all@gmail.com
Anonymous Axaptian