Search code examples
sqloracleoracle12coracle-data-integrator

How to make a string for the group by field in Oracle Data Integrator 12c

I am a newbie in ODI 12c. I have recently installed it and did some tests on it. I have a table like this:

enter image description here

My goal is that I want to group by on customer_id and make a JSON format string for each customer_id. In Oracle database, I can do that with this query as following below:

  select customer_id,'[' || listagg('{"TRX_ID":' 
    || '"' || trx_id || '"' || ',"count_rules":' 
    || '"' || count_rules || '"'  
    || '}',',') within group(order by count_rules) || ']' as JSON_RULES
  from (select customer_id,trx_id,count(rules) as count_rules from test_rules group by 
  customer_id,trx_id) group by customer_id

The result is like this:

enter image description here

However, I want to do the same work in ODI 12c, would you please guide me how I can do that?

Any help is really appreciated.

Solution

  • The aggregate component in a mapping can be parametrized to use a custom GROUP BY clause if needed.

    Here is how to use this component :

    1. Drag and drop an aggregate component from the component pane to the mapping canvas
    2. Drag CUSTOMER_ID column from source datastore to the aggregate component
    3. Click on the aggregate component, go on the attributes tab and add a new column JSON_RULES enter image description here
    4. Click on that new attribute in the mapping canvas and set the expression '[' || listagg('{"TRX_ID":' || '"' || trx_id || '"' || ',"count_rules":' || '"' || count_rules || '"' || '}',',') within group(order by count_rules) || ']'
    5. By default the aggregate component will set the Is Group By property of all attributes to Auto. Auto means that all attributes that DON'T have an aggregate function in their expression will be part of the GROUP BY clause. So in your case only CUSTOMER_ID should be in the GROUP BY clause and you should be fine. If the JSON_RULES attribute is mistakenly added in the GROUP BY clause, you can still set the Is Group By property to No.
    6. In case of a need for more advanced GROUP BY clause, it's still possible to manually set one in the General tab of the aggregate component. It is also where wecan set an HAVING clause if needed enter image description here

    [EDIT] I didn't see you had a subquery in there. You just need to put a first aggregate component before the one I showed to aggregate by CUSTOMER_ID and TRX_ID.