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).
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.
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.
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.