Is there any EQUIVALENT function of "XMLAGG"
, as performance of XMLAGG
is very bad.
sample:sql
SELECT
LOAN_APLCTN_KEY
,CAL.COB_DT AS STRT_DT
,CAL.NXT_COB_DATE AS END_DT
,CAL.COB_RUN_END_TM
,TRIM(TRAILING '0A'XC FROM (XMLAGG(TRIM(FREE_MEMO)|| '0A'XC ORDER BY M_INDX ASC) (VARCHAR(2500) , CHARACTER SET UNICODE ) ) ) AS FREE_MEMO
FROM
(
SELECT A.* ,
A.NXT_WRKG_DY AS NXT_COB_DATE
FROM BUSNSVLT_VW.BV_COB_DATES A
WHERE CALENDAR_DATE BETWEEN DATE '2019-11-01' AND DATE '2019-11-10'
QUALIFY ROW_NUMBER () OVER (PARTITION BY COB_DT, COB_RUN_END_TM ORDER BY COB_DT DESC ) =1
) CAL
INNER JOIN
(
SELECT *
FROM DATAVLT_VW.T24_SAT_LOAN_APLCTN_FREE_MEMO AA
WHERE M_INDX >0
AND PERIOD ( CAST ( DATE '2019-11-01' - INTERVAL '1' DAY AS TIMESTAMP (6) ) , CAST ( DATE '2019-11-10' + INTERVAL '1' DAY AS TIMESTAMP (6) ) )
OVERLAPS ( AA.LD_DT , AA.LD_ENDDT )
)AA
ON CAL.COB_RUN_END_TM BETWEEN AA.LD_DT AND AA.LD_ENDDT
GROUP BY LOAN_APLCTN_KEY
,CAL.COB_DT
,CAL.NXT_COB_DATE
,CAL.COB_RUN_END_TM