Aggregate Functions: Differences Between X++ and SQL

Whenever the where clause of a query does not match any rows, the SQL standard stipulates that one row is to be returned, in which count-columns return the value 0 (zero), and all other aggregated columns return SQL value null.

Because Microsoft Dynamics AX does not support the concept of null values, a slightly different approach is taken. Whenever a row returned from the Microsoft SQL Server database contains a null value from an aggregated column (for example, SUM(f)), the row is skipped. No rows are returned to the user.

However, if count, which does not return null values, is the only aggregate function that is used, one row is returned (as stated in the ANSI SQL standard) with 0 (zero) value or values.

However, if null is returned from an ordinary column, an error exception is thrown with the following message:

"Unsupported null value selected from the database."

Although Microsoft Dynamics AX does not support null values in the database management system sense, each of the different data types have a specific value that is treated like a null value in certain circumstances.

Community Additions