I was creating way how to solve problem with duplicate code, street_name and street_number.
so ther's duplicate code querys:
I have tableA(code, street_name,street_number, att1, att2) and theres duplicate code, street_name and street_numbers.
I was found duplicate code using:
select code from tableA where code in (select code from tableA group by code having count(*) >1)
but in meantime, code was changed and I have other code and old street_name and old street_number,
so tableA now have :
oldCODE,street_name, street_number and newCODE, street_name, street_number --- duplicate street_name and street_number!.
tableA have 7000 records.
Question is how found this duplicate street_name and street_number with this different code (old and new CODE)?
select STREET_NAME, STREET_NUMBER, max(ltrim(sys_connect_by_path(CODE,' - '),' -')) keep (dense_rank last order by length(sys_connect_by_path(CODE,' - ')))duplicate_code
connect by prior CODE < CODE
and prior street_name =street_name
and prior street_number = street_number
group by STREET_NAME, STREET_NUMBER
And ther's list of Street_Name, Street_Number, duplicate_CODE
100 of 15000 records!
So need update old code as 'NO ACTIVE'
or 'OLD VALUES'