Computed Columns in Views

A view is a selected number of fields that come from one or more related tables.
Microsoft Dynamics AX can also display a computed column based on fields in
those tables. A computed column generates T-SQL code at compile time and
adds it to the T-SQL code generated by the view, which increases performance.

During the following demonstration you examine a computed column in the
standard application.
1. Open the AOT, expand the Data Dictionary > Views node.

2. Find the InventTableExpanded node

3. In the Fields node, find the ProductName field, and open the
properties sheet.

4. Note the property ViewMethod is set to productName. This refers
to a static method on the view.

5. Expand the Methods node on the InventTableExpanded view.

6. Open the productName method. The code in the method is as
follows:

1 2 3 4 5 6 7 8 9 10 11 12 13

TableName viewName = identifierstr(InventTableExpanded); str translatedNameField = SysComputedColumn::returnField(viewName, identifierstr(EcoResProductTranslations), fieldstr(EcoResProductTranslations, ProductName)); str productDisplayNumberField = SysComputedColumn::returnField(viewName, identifierstr(EcoResProduct), fieldstr(EcoResProduct, DisplayProductNumber)); return SysComputedColumn::if( SysComputedColumn::isNullExpression(translatedNameField), productDisplayNumberField, translatedNameField);

7. The three variables that are declared are used to hold the physical
names of the view and the fields that are to be used.
8. The SysComputedColumn::if() method then converts three
expressions in to the T-SQL expression. By placing the cursor on the
if() method and pressing F12, you can see the method.
9. To view the final T-SQL code that is generated, you need to turn on
SQL-Tracing. Go to Tools > Options > SQL.
10. Check SQL Trace, and then check Multiple SQL Statements >infolog.
11. Close the user options form.
12. In the AOT, right-click on view InventTableExpanded and selectSynchronize.
13. An infolog box is displayed with a number of SQL commands. One
of them is creating the view and is as follows:
 

14. Note that one of the fields has the following : ,(CAST ((CASE
WHEN T5.PRODUCTNAME IS NULL THEN
T2.DISPLAYPRODUCTNUMBER ELSE T5.PRODUCTNAME
END) AS NVARCHAR(60))) AS PRODUCTNAME,


Best Regards,
Hossein Karimi