Deduplicating and Creating Lists – Oracle SQL LISTAGG

How do you leverage LISTAGG from ODI? How do you aggregate rows into strings or lists? Let’s see! Here we will see a few things including LISTAGG, an Oracle Analytic SQL function useful for creating lists from rows of information. We will also see how ODI can be extended to recognize this special function and generate the code we desire.

The data in our example has many order numbers for each customer. What we want is a single row for the customer and a comma separated list of order numbers for example. The LISTAGG function is perfect for this, and is blogged about all over the place. The example we will build in ODI takes the data from a source table and creates a comma separated list of order numbers;

To get ODI to recognize LISTAGG as an aggregation function we extend the ODI language elements in the Topology by adding a LISTAGG entry and define it as a ‘Group Function’ as below (I defined one implementation for Oracle and switched off the Universal flag). This will enable the ODI built-in aggregation analysis, so we get the group by generated automatically.

The ODI interface we will define looks like the following – we simply define the LISTAGG expression as an expression in the target column mapping and the CUSTID column will be the group by!

I did come across a limitation which would have been better had it not been there for user functions – they cannot include aggregation expressions, otherwise we could wrapper this up in a nice friendly user function (avoiding the technology specific grammar).

Other systems have such aggregation and list creation capabilities – for example if you look around Hypersonic SQL added GROUP_CONCAT for example to do similar list creation stuff, many others seem to be derived from the XMLAGG functions which build XML structures from relational.

The LISTAGG function is a useful function to remember.

Comments:

Are you planning to put this in official ODI release.
On a side note, where can one find documentation on how to use the functions defined in the Grammar.

Posted by guest on August 07, 2012 at 07:39 AM PDT #

You can find the functions in the Oracle Database SQL Language Reference, the link to LISTAGG is below;
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm#CJABDFBD

The function should really be seeded in ODI.

Cheers
David

Posted by David on August 07, 2012 at 07:53 AM PDT #

Hi guys,
I use this feature in ODI 11.1.1.6, but I have a problem. I use two work repositories (DEV and TEST), use same master repository.
on DEV all works well, but when I create version of interface, restore him on TEST, and try simulation or execution, group clause not added into code. I try all, sometime is group clause added, after save or another version restore of this, not works.
One important thing...I use this function in virtual interface (which is used as subselect source from another)
Have you any ideas, how resolve this issue ?
Thanks a lot

Posted by Petr Simbera on January 22, 2013 at 02:10 AM PST #

Hi Petr

Is the group by always omitted after a restore on TEST?

Cheers
David

Posted by David on January 22, 2013 at 08:38 AM PST #

hi David,
i am facing errors as i already followed the approach you have specified, please help me regarding this
i am facing below error..

Caused By: java.sql.SQLSyntaxErrorException: ORA-00937: not a single-group group function

Regards,
Hari

Posted by guest on July 07, 2013 at 11:55 PM PDT #

Hi Hari

It sounds like you have some columns generated in the SQL that are not included in the group by. Worth inspecting the generated SQL to see if you can spot what is wrong. Copy the SQL in the comments here if you are struggling.

Cheers
David

Posted by David on July 08, 2013 at 07:13 AM PDT #

Hi there
am trying this but the grouping is not working. Can you tell me what is in that Expression box (snip cuts off full syntax!). all I can read is "LISTAGG($(field)s)"

Posted by Charlie on April 01, 2014 at 03:01 PM PDT #

Hi Charlie

That is the full expression I think. Are you defining the language element? With the correct properties set?

I can try it again later, let me know if you still have problems.
Cheers
David

Posted by David on April 01, 2014 at 05:35 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today