I'd use a listagg()
to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that sometimes a customer can have too many components and exceed the maximum output of listagg()
. I was wondering if there could be a way to generate a hash from a group of ordered rows, in a way I could use to make the same validation. Sort of:
with my_tabe as
(
select 'user1' as usrid, '1' as prodcode from dual union
select 'user1' as usrid, '2' as prodcode from dual union
select 'user1' as usrid, '3' as prodcode from dual union
select 'user2' as usrid, '2' as prodcode from dual union
select 'user2' as usrid, '3' as prodcode from dual union
select 'user2' as usrid, '4' as prodcode from dual
)
select
userid,
ORA_HASH(prodcode) WITHIN GROUP (ORDER BY prodcode)
from
my_table
group by userid
Is there any function that could be used to that end?