0

listagg function in Incorta

Hi,

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))

9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • You just gave me a part of the sql, can you give me the full sql so that I can understand what you are trying to do?

    Like
  • This is the how the logic is defined in OBIEE, Business is taking Invoice Sales Order numbers  as "billing agreement number" from a dimension table.

    so when you backtrack the column to ERP, this is logic defined.

    select 

    LISTAGG( RA_CUSTOMER_TRX_LINES_ALL.ATTRIBUTE14,',') 
    WITHIN GROUP ( ORDER BY RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID) 
    OVER (PARTITION BY RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID)  as "billing_order_number
     from

    RA_CUSTOMER_TRX_LINES_ALL

    please refer to screenshot, where i have attached the sample data from OBIEE Report.

    Like
  • Hi,

     

    I tried workaround, like putting this query directly in incorta and tried to load it.

    "but incorta says string concatenation is too long".

    Below is the query i have used to load the column as new table.

     

    select TRX.CUSTOMER_TRX_LINE_ID,LISTAGG(CHG.PURCHASE_ORDER_NUMBER,',') 
    WITHIN GROUP ( ORDER BY TRX.CUSTOMER_TRX_ID) 
    OVER (PARTITION BY TRX.CUSTOMER_TRX_ID) as PO_NUMBER
    from apps.EQX_RA_CUST_CHARGE_LINES_ALL CHG
    LEFT OUTER JOIN 
    apps.RA_CUSTOMER_TRX_LINES_ALL TRX
    ON CHG.CUSTOMER_TRX_LINE_ID = TRX.CUSTOMER_TRX_LINE_ID
    where CHG.CREATION_DATE >= '01-Jan-18'

    Please find the attached screenshot with the error message

    Like
    • Ram You are exceeding the SQL limit of 4000 bytes which applies to LISTAGG as well so try wrapping it around a substr() function to just select the first 4k characters in the above extract sql, another way to do that is via an MV as listed below.

      Like
    • Amit Kothari 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
    • Amit Kothari i tried this in MV, but while trying to save it shows blank screen as error, not sure whats gone wrong.attaching screenshot for your ref.

      Below is the query i have used to save the mv.

      select distinct CHG.CUSTOMER_TRX_LINE_ID,
      concat_ws(';', collect_list(CHG.ATTRIBUTE14) OVER (PARTITION BY CHG.CUSTOMER_TRX_ID)) as DESCRIPTION
      from EBS_AR.RA_CUSTOMER_TRX_LINES_ALL CHG

      Like
    • Ram Can you try to run this query from dbvisualiser  use the postgres sql option to connect to incorta on port 5442 ? 

      Like
  • Amit Kothari  Hi, I tried executing in the db visualizer, where it keeps running more than an hour just for a single transaction id.Please find the attached SS for reference.

    Like
    • Ram  let us meet, can you send me a mail?

      Like
Like Follow
  • Status Answered
  • 5 mths agoLast active
  • 9Replies
  • 42Views
  • 2 Following

Product Announcement

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