listagg function in Incorta
Can you please tell me,on how to implement the below listagg function in Incorta
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))
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.
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
please refer to screenshot, where i have attached the sample data from OBIEE Report.
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.
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
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