User defined SQL functions in ODI

As most of you know there are some SQL functions(SUM,COUNT,AVG etc) will be in ODI repository to transform/compute source data. If we want to add new functions to this list ,you can follow steps.

Note: We will create LISTAGG function which is a list(rows) aggregate function and orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

 Syntax:

LISTAGG (measure_column [, 'delimiter'])

  WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

Let see how it can be created in ODI

1.Navigate to Topology-->Languages-->SQL-->Aggregate

2.Create New function and name it as LISTAGG and enter other details as following

Enter expression as

LISTAGG($(expr)n1, $(orderby)n2)

and make sure "Group Function" checkbox checked .Then only it will be considered as aggregate function and generates proper group by statement.

3. Navigate to Implementation and click + symbol to add.
Here provide exact syntax  and choose the appropriate technology.

LISTAGG($(expr)n1,',') WITHIN GROUP(ORDER BY $(orderby)n2)

4. Save and close
5.Check if that aggregate function is appearing in column expression with orange color (If this function recognized then it will be visible in orange color)

Now ,you can reuse this function anywhere in odi by just modifying parameters.


Similarly XMLAGG can be implemented in same fashion.

Expression under definition

XMLAGG($(expr)n1, $(orderby)n2)

and in Implementation

XMLAGG(XMLELEMENT("E",$(expr)n1,',').EXTRACT('//text()') ORDER BY $(orderby)n2)

Example to truncate morethan 3900 characters in CLOB type column

DBMS_LOB.SUBSTR(
         rtrim(xmlagg(
           xmlelement( e,WO_TSK_DESCRIPTION || ' ' )  ORDER BY GEN_ARG
         ).extract( '//text()' ).getClobVal(),
         ' '
       ) , 3900,1) WO_TASK_NOTE_DESC