Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Multiple listaggs with overflow

Prokopios PoulimenosFeb 6 2024 — edited Feb 6 2024

Hello,
I have a problem with a query concatenation.

The query is the below :

select mov_send, mov_rec, msg_list, send_list, rcv_list, msg906, msg907, msg917, count(*) total
    , listagg(substr(mrn, 1, 5), ',')
    , listagg(substr(mrn, 6, 5), ',')
    , listagg(substr(mrn, 11, 4), ',')
    , listagg(substr(mrn, 15, 4), ',')
 from (select mov_send, mov_rec, mrn
            , listagg(substr(msg_type, 3,4), ''  ) WITHIN GROUP (ORDER BY SEND_DATE) msg_list
            , listagg(msg_send, '' ) WITHIN GROUP (ORDER BY SEND_DATE) send_list
            , listagg(msg_rec, '' ) WITHIN GROUP (ORDER BY SEND_DATE) rcv_list
            , sum(case when msg_type like '%906%' then 1 else 0 end) msg906
            , sum(case when msg_type like '%907%' then 1 else 0 end) msg907
            , sum(case when msg_type like '%917%' then 1 else 0 end) msg917
            , sum(case when msg_type like '%916%' then 1 else 0 end) msg916 
        from (SELECT a.SENDER mov_send, a.RECEIVER mov_rec, b.MSG_TYPE msg_type, b.SENDER msg_send
                   , b.RECEIVER msg_rec, COALESCE(a.MRN, a.CRN) mrn, b.SEND_DATE
                 FROM MOVEMENT a 
                      INNER JOIN MOVEMENT_MESSAGE b on a.ID = b.MOVEMENT_ID 
                where MOVEMENT_ID in (SELECT movement_id 
                                        FROM movement_message 
                                       WHERE (msg_type LIKE '%906%' OR msg_type LIKE '%907%' OR msg_type LIKE '%917%') 
                                         AND IS_IECA = 0 
                                        AND send_date BETWEEN to_date('2024-01-06', 'YYYY-MM-DD') and to_date('2024-02-06', 'YYYY-MM-DD')
                                      ) 
   AND IS_IECA = 0 
   AND DOMAIN = 'ECS') q 
   group by mov_send, mov_rec, mrn) q1 
   group by mov_send, mov_rec, msg_list, send_list, rcv_list, msg906, msg907, msg917, msg916 
   order by total desc 
   OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
    

The query raises String concatenation is too long for msg_list.
So I modified the query to

select mov_send, mov_rec, msg_list, send_list, rcv_list, msg906, msg907, msg917, count(*) total
    , listagg(substr(mrn, 1, 5), ',')
    , listagg(substr(mrn, 6, 5), ',')
    , listagg(substr(mrn, 11, 4), ',')
    , listagg(substr(mrn, 15, 4), ',')
 from (select mov_send, mov_rec, mrn
            , listagg(substr(msg_type, 3,4), '' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY SEND_DATE) msg_list
            , listagg(msg_send, '' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY SEND_DATE) send_list
            , listagg(msg_rec, '' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY SEND_DATE) rcv_list
            , sum(case when msg_type like '%906%' then 1 else 0 end) msg906
            , sum(case when msg_type like '%907%' then 1 else 0 end) msg907
            , sum(case when msg_type like '%917%' then 1 else 0 end) msg917
            , sum(case when msg_type like '%916%' then 1 else 0 end) msg916 
        from (SELECT a.SENDER mov_send, a.RECEIVER mov_rec, b.MSG_TYPE msg_type, b.SENDER msg_send
                   , b.RECEIVER msg_rec, COALESCE(a.MRN, a.CRN) mrn, b.SEND_DATE
                 FROM MOVEMENT a 
                      INNER JOIN MOVEMENT_MESSAGE b on a.ID = b.MOVEMENT_ID 
                where MOVEMENT_ID in (SELECT movement_id 
                                        FROM movement_message 
                                       WHERE (msg_type LIKE '%906%' OR msg_type LIKE '%907%' OR msg_type LIKE '%917%') 
                                         AND IS_IECA = 0 
                                        AND send_date BETWEEN to_date('2024-01-06', 'YYYY-MM-DD') and to_date('2024-02-06', 'YYYY-MM-DD')
                                      ) 
   AND IS_IECA = 0 
   AND DOMAIN = 'ECS') q 
   group by mov_send, mov_rec, mrn) q1 
   group by mov_send, mov_rec, msg_list, send_list, rcv_list, msg906, msg907, msg917, msg916 
   order by total desc 
   OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
    

Businesswise each message type (msg_type) represents a communication exchanged between a sender and a recipient. The issue I'm encountering is that while the msg_type is concatenated with 4 characters, both the sender and recipient fields consist of only 2 characters each.
Consequently, when using functions like listagg(msg_send) and listagg(msg_rec), more rows are being concatenated than expected.

If I concatenate 998 msg_types, I want also to concatenate 998 msg_send and 998 msg_rec.

So, I think that the second and third clause ‘on overflow truncate’ can be avoided.

Could you help me please ?

This post has been answered by Stax on Feb 6 2024
Jump to Answer
Comments
Post Details
Added on Feb 6 2024
2 comments
88 views