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++):
If you would like to see the total amount of the salesorder in the same result set you would need to do a “GROUP BY”:
in X++ you would do something like this:
But it will send a statement for each salesline to the database.
A better way to do this in Ax would actual be:
- Create a Query object for the ‘select sum(LineAmount), SalesId from salesLine’ bit.
- Put that Query in a View.
- Now create a new Query with in the datasource the SalesLine and join it with the View.
Now let’s make it even more interesting. I would like a statement to get a resultset with the following columns:
- the SalesId, ItemId, SalesCategory, CustAccount, LineAmount.
- the total amount of the salesorder.
- the avg LineAmount of the customer in the same category.
- the LineAmount of the last time this customer ordered this item.
- running total of this customer.
And here we are, our first window functions. They are called window functions because they put a window (subset) over the rows you get out of your FROM clause.
The <window function> part can be: Ranking functions, Aggregate functions or Analytic functions.
After the OVER keyword you can limit the size of the window:
- OVER() isn’t limiting at all so all rows are considered.
- OVER (PARTITION BY X) means limit the window to al rows that have matching values in Column X in current Row.
- OVER (ORDER BY) changes the sort order in the window. (Can be nice if you use RANK or ROW_NUMBER window functions)
- OVER( ROWS BETWEEN … AND …) limits the window to the rows between the given criteria seen from current row.
How to use these powerful window functions in Dynamics AX?
As I mentioned before X++ isn’t a real options. But in the View objects in the AOT there is a little gem that we can polish for our purpose.
- Create a Query for the SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE part.
- Use this Query in a new View.
- Create a method on the View with the following code:
- Now right click on the ‘fields’ branch of the view choose NewReal computed column.
- Give the new field a nicer name and set the name of the method in the ‘viewmethod’ property.
- Save and synchronise the view.
Now what did we just do?
We changed the create statement of the view to include ‘SUM(LINEAMOUNT) OVER (PARTITION BY SALESID)’ as a column. By synchronizing we created a view in SQL Server that contains the window functions we want to.
Could we use this for other things then window functions?
Yes you can use all possibilities of the T-SQL language here. But remember you are changing the Select clause of the view here. Putting a select statement in here (or a stored procedure) will for sure make the view run a long time as every row will trigger a separate statement. But a scalar value function can be very valuable sometimes (like ISNULL()).