Using LISTAGG and other meta columns in ODI interfaces

I recently needed to use the LISTAGG function in a custom Oracle ODI interface. However to my dismay I found that ODI didn’t recognise LISTAGG as an aggregate function (i.e. didn’t add a GROUP BY clause to the SQL generated).

No Group By

After a bit of searching on Google I found I wasn’t the only person to have hit this and found this blog post which explains a method for adding the function to the language in the ODI topology. I tried and failed – no matter what I did I couldn’t get it to work.
So instead I tried a little trick I have used in the past for something simlar – to add a commented out aggregate function in the column source.

ODI LISTAGG


 

Now when the SQL is generated a group by is added – and the commented out expression of course as no effect on the SQL run.

ODI with Group By

It’s probably exploiting a bug in ODI rather than utilizing a feature (and hence may be ‘fixed’ at some point in the future) so use at your own judgement.
The same technique can be used though for surfacing metacolumns in a model – for example ROWID – simply add a column that exists into the expression but commented out.

/*MY_TAB.ID*/ MY_TAB.ROWID 

This makes it particularly useful for creating joins between descriptive flex views with an E-Business Suite source.

Hope this is a help to anyone who is currently facing similar issues.

Leave a Reply

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

six − 5 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.