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.

Window Functions in all SQL Versions

Window Functions in all SQL 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 to do a “GROUP BY”:

SELECT SALESLINE.SALESID,  ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE

JOIN (SELECT SUM(LINEAMOUNT) AS Total, SALESID FROM SALESLINE GROUP BY SALESID) AS A ON A.SALESID = SALESLINE.SALESID;

in X++ you would do something like this:

While Select SalesId,  ItemId, SalesCategory, CustAccount, LineAmount from SalesLine
 {
     select sum(LineAmount) from salesLine2 where salesLine2.SalesId == salesLine.SalesId;
 }

But it will send a statement for each salesline to the database.

TIP

A better way to do this in Ax would actual be:

  1. Create a Query object for the ‘select sum(LineAmount), SalesId from salesLine’ bit.
  2. Put that Query in a View.
  3. Now create a new Query with in the datasource the SalesLine and join it with the View.
Calling this Query will actually make SQL Server solve your question in a similar way as the SQL statement I wrote down earlier.

Now let’s make it even more interesting. I would like a statement to get a resultset with the following columns:

  1. the SalesId,  ItemId, SalesCategory, CustAccount, LineAmount.
  2. the total amount of the salesorder.
  3. the avg LineAmount of the customer in the same category.
  4. the LineAmount of the last time this customer ordered this item.
  5. running total of this customer.
In X++ this would be just a nightmare to program. with the tip I gave you earlier it could be done but this would create a lot of objects in your AOT.
In SQL you could go the way of create a lot of subqueries with GROUP BY and get your result set. OR… you could write the following statement:
SELECT SALESID, 
ITEMID, 
SALESCATEGORY, 
CUSTACCOUNT, 
LINEAMOUNT,
SUM(LINEAMOUNT) OVER (PARTITION BY SALESID) AS [Total amount salesorder],
AVG(LINEAMOUNT) OVER (PARTITION BY CUSTACCOUNT, SALESCATEGORY) AS [Avg LineAmount customer category],
LAG(LINEAMOUNT,1) OVER (PARTITION BY CUSTACCOUNT,ITEMID ORDER BY SALESID) AS [Last time ordered for],
SUM(LINEAMOUNT) OVER (PARTITION BY CUSTACCOUNT ORDER BY SALESID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total];

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 functions are written like:
<window function> OVER (<PARTITION BY><ORDER BY><ROWS BETWEEN…. AND …..>)

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.

    1. Create a Query for the SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE part.
    2. Use this Query in a new View.
    3. Create a method on the View with the following code:
      public static server str windowFunction()
      {
          //make sure you use this Dictview because the names of the tables are changed in the SQL statement send from Ax to SQL.
          //This makes sure the right names are used
          DictView dv = new DictView(tableNum(<<name of view>>));
      
          //This is the real magic 
          return 'SUM('+dv.computedColumnString('SalesLine', 'LineAmount') +') OVER ( PARTITION BY '
                  + dv.computedColumnString('SalesLine', 'SalesId');
      }
      
    4. Now right click on the ‘fields’ branch of the view choose NewReal computed column.
    5. Give the new field a nicer name and set the name of the method in the ‘viewmethod’ property.
    6. 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()).
Interesting links:

A 20 minute training about window functions in SQL 2012.

All code samples, walkthroughs and other instructions are provided as-is. Use any and all information provided in this blog at your own risk. It is never advised to import or write code in a production environment without rigorous testing in a test or development environment.
All opinions expressed in this blog are solely my own and do not necessarily reflect the opinions of my employer.
The names of actual companies and products mentioned in this blog may be the trademarks of their respective owners.
Microsoft and Dynamics AX are registered trademarks of Microsoft Corporation in the United States and other countries.
Comments1

Leave a Comment!

Your email address will not be published. Required fields are marked *