2

How to Implement oracle LISTAGG() function in Incorta?

For a specified measure, Oracle's LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column - how do we implement the LISTAGG() function in Incorta?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • We can implement this using Spark sql in a Materialized view  using the  collect_list() function and a group by-

    eg , the tax_rate_code below is a concat of all the child values - 

    ---MV SQL Code---------

    select distinct CHG.CUSTOMER_TRX_ID,
    concat_ws('; ', collect_list(TRX.DESCRIPTION) OVER (PARTITION BY TRX.CUSTOMER_TRX_ID ORDER BY TRX.DESCRIPTION ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) as DESCRIPTION
    from EBS_AR.RA_CUSTOMER_TRX_ALL CHG
    LEFT OUTER JOIN 
    EBS_AR.RA_CUSTOMER_TRX_LINES_ALL TRX
    ON CHG.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
    where 1=1;
    -------

    Like 1
  • Hi Amit,

    Can you please tell me,on how to implement the below listagg function in Incorta

    LISTAGG( RA_CUSTOMER_TRX_LINES_ALL.ATTRIBUTE14,',') 
    WITHIN GROUP ( ORDER BY RA_CUSTOMER_TRX_LINES_ALL.TO_CHAR(CUSTOMER_TRX_ID)) 
    OVER (PARTITION BY RA_CUSTOMER_TRX_LINES_ALL.TO_CHAR(CUSTOMER_TRX_ID))

    Like
    • Ram For sorting the elements, we can use the sort_array() function from Spark:

       

      select
          TARGET_NAME,
          CONCAT_WS(',', SORT_ARRAY(COLLECT_LIST(MONTH_NAME),true)) MONTH_LIST
      FROM
          (
              SELECT
                  DISTINCT TARGET_NAME,
                  date_format(START_DATE, 'MMM') MONTH_NAME
              FROM
                  _IncortaMetadata.JOB
              WHERE
                  TARGET_NAME = 'EBS_FA'
          )
      GROUP BY TARGET_NAME
      
      Like
  • I tried this in MV and it does work so you can modify as per your sql - 

    select distinct CHG.CUSTOMER_TRX_ID,
    concat_ws('; ', collect_list(TRX.DESCRIPTION) OVER (PARTITION BY TRX.CUSTOMER_TRX_ID)) as DESCRIPTION
    from EBS_AR.RA_CUSTOMER_TRX_ALL CHG
    LEFT OUTER JOIN 
    EBS_AR.RA_CUSTOMER_TRX_LINES_ALL TRX
    ON CHG.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
    where CHG.CUSTOMER_TRX_ID=852224;

    Like
Like2 Follow
  • 2 Likes
  • 1 mth agoLast active
  • 4Replies
  • 437Views
  • 3 Following

Product Announcement

Incorta 2014.2 is now available!