I am trying to do a vertical pivoting in Oracle using the LISTAAG functions.
The Query I am using is
select MEMBERS,GROUPBP,rownum as Set_Id,length(MEMBERS) as MEMBERS_LENGTH
LISTAGG ( objc_sap_bp_id,'|') WITHIN GROUP
(ORDER BY objc_sap_bp_id) GROUPBP
LISTAGG ( SUBJ_SAP_BP_ID,'|') WITHIN GROUP
(ORDER BY SUBJ_SAP_BP_ID) MEMBERS
where bp_rel_catg_code='XXXX' and objc_sap_bp_id not in ('XXX','YY')
and valid_to_date = to_date('99991231','YYYYMMDD')
group by objc_sap_bp_id )
group by MEMBERS)
WHERE length(GROUPBP) > 10
It works if the MEMBERS list is less than the maximum column size that Oracle can accomodate but if it grows than the required limit , it crashes because the length of the column is too big for the Oracle to accomodate.
Is there any way that I can avoid the Query to crash if the limit exceeds more than what is required.
I simply want to avoid those one's for which the member list is too big.
I tried substring but it did not help.