Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
If I read you correctly, you're trying to compare a comma-separated list of components by customer (that is stored outside the database) with the same data that's stored sensibly in the database. Wouldn't you be better off splitting the source string and then using unpivot, allowing for an easier comparison? –  FreshPhilOfSO Sep 27 '12 at 16:43
    
You read it correctly, but it isn't the whole story. I've made it short so I could explain the question, but this comparison is just part of a whole process which isn't even executed by Oracle. What you said is probably a better idea in most cases but here I'd just want to generate the hashes to feed my other process (responsible for the comparison). –  filippo Sep 27 '12 at 16:47
    
cross-posted on SO stackoverflow.com/questions/12623215/… –  bluefeet Sep 27 '12 at 18:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.