SQL Window Functions in Ax? Yes we can

Are SQL Window Functions new? Since 2003 in the SQL ANSI/ISO standard and extended in 2008. Microsoft implemented the first window functions in their SQL Server 2005 version (known as OVER clause) and extended them in the following versions. So to be short no they are not new for the ANSI/ISO standard and for Microsoft SQL Server window functions exist for a few version now. Within in Dynamics Ax we use X++ which is translated by the Dynamics Ax Kernel to T-SQL to make the database return a result set. This makes us depended of the implementation of X++, which in Dynamics Ax 2012 doesn’t support window functions for select statements etc. But I can show you a way on how to use them in AX 2012 and up. What are SQL Window Functions? Let me show you with an example. As this is a blogpost for Dynamics Ax minded people let us use the SalesLine table. If you want to select a few fields of this table, you would write something like the following (in SQL and X++): SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE; If you would like to see the total amount of the salesorder in the same result set you would need […]

The post SQL Window Functions in Ax? Yes we can appeared first on Kaya Consulting | Microsoft Dynamics AX.