HAVING CLAUSE

So lets say you had a table of sales figures by date and customer, and you are asked to populate a new table that will contain just those customers who have bought more than $10,000 worth of merchandise from the company.

If you were going to do this in SQL it would be pretty straight forward. You would sum up the total sales per customer, and then only return those customers having total sales of more than $10,000.

eg. SELECT customer, sum(salesamount) FROM sales GROUP BY customer HAVING sum(salesamount) > 10000;

Now Data Services has the very useful Query transform that does nearly everything we need, but not quite.

If we have a look at the tabs available to us in the query transform we have a GROUP BY tab, but no HAVING tab.

So how do we get around this?

Well the first step is to create a query the gets the total sales per customer, grouping on customer name, and summing the sales amount.

Then pass the results from that query into a second query that filters down the results based on the results from the first query.

Not exactly rocket science, which is why I wish that SAP would just add the HAVING clause into the query transform. There are two main reasons why having the clause in the transform would be nice:

1. It would make data flows a bit neater. When you have a massive collection of transforms in a complex data flow, the last thing you need is extra transforms on the screen just to carry out a fairly trivial task.
2. The HAVING clause does not get pushed down. Take a look at the SQL produced by the above data flow:

Just the group by clause is getting pushed down, so you may end up bringing millions of rows onto the Data Services server, and then only return a handful into your target.

So if any-one from SAP is reading this blog, please give us a HAVING clause.