Working with analytic functions in ODI

As you all know that Analytic functions(SUM() OVER, RANK,ROW_NUMBER etc) will be executed in a sql query after all JOINS,WHERE,GROUP BY and HAVING clause except ORDER BY. These functions are the last set of operations performed on group of rows or window of rows. And most important point is that these wont need GROUP BY clause unlike normal aggregate functions.

Here the problem is when we add any analytic function(ex: sum(sal) over (order by empno)) in ODI column expression then ODI parser treats it as normal aggregate since it parsed SUM in expression and appends remaining columns into group by clause. But group by does not supported since it has analytic function in the select list. In order to solve this we need to force ODI parser to treat this column as normal column.
Here is the way to hide column being returned from getGrpBy() method which used by odi parser.

Just use below code around to your analytics function to prevent it from considering as normal aggregate function.

example.
SUM(sal) OVER (ORDER BY empno),
MAX(sal) OVER (PARTITION BY deptno)

We can write them in ODI column expression as

<?out.print("SUM");?>(sal) OVER (ORDER BY empno),
<?out.print("MAX");?>(sal) OVER (PARTITION BY deptno)

Above statement will print the SUM or MAX after getGrpBy() method execution and in the final generated query will have analytic function without any columns in group by clause.