In a standard Dynamics AX application, there are macros like InventDimJoin and InventDimSelect, which are reused numerous times across the application. Those macros are actually full or partial X++ SQL queries, which can be called with various arguments. Such approach saves developing time and makes upgrades easier.
In this recipe, we will create a small macro, which holds a single where clause to display only active vendor records. Then, we will create a job which uses created macro for displaying a vendor list.
Open AOT, and create a new macro called VendTableNotBlocked with the following code:
(%1.Blocked == CustVendorBlocked::No)
In AOT, also create a new job called VendTableMacro with the following code:
static void VendTableMacro(Args _args) { VendTable vendTable; ; while select vendTable where #VendTableNotBlocked(vendTable) { info(strfmt( "%1 - %2", vendTable.AccountNum, vendTable.Name)); } }Run the job, and inspect the results, as displayed on the screen:
First, we define a macro that holds our where clause. Normally, the purpose of defining SQL in a macro is to reuse it a number of times in various places. We use %1 as an argument. More arguments could be used.
Next, we create a job with the select statement. Here, we use the previously created macro in a where clause. We pass vendTable as an argument.
The query works like any other query, but the advantage is that the code in the macro could be reused elsewhere.
Note that although using a macro in a SQL statement can reduce the amount of code, too much code in it might reduce the SQL statement's readability for other developers. So keep it balanced.