0

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?

3replies 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    FACT.LINK_TO_CUST_TRX_LINE_ID AS   LINK_TO_CUST_TRX_LINE_ID , 
        MAX(FACT.TAX_LINE_ID) AS TAX_LINE_ID,
        SUM( FACT.EXTENDED_AMOUNT) AS TOTAL_TAX_AMOUNT,
        MAX(ZV.TAX_RATE) AS TAX_RATE,
        concat_ws(', ', collect_list(ZV.TAX_RATE_CODE)) AS TAX_RATE_CODE,
        MAX(ZV.TAX_REGIME_CODE) AS TAX_REGIME_CODE
    FROM    EBS_AR.RA_CUSTOMER_TRX_LINES_ALL  AS FACT LEFT OUTER JOIN EBS_ZX.ZX_LINES_V AS ZV
    ON FACT.TAX_LINE_ID = ZV.TAX_LINE_ID
    WHERE  FACT.LINE_TYPE = 'TAX'
    GROUP BY FACT.LINK_TO_CUST_TRX_LINE_ID
    -------

    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
  • 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
Like Follow
  • 5 mths agoLast active
  • 3Replies
  • 334Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!