понедељак, 25. август 2008.

duplicate code, street_name and street_number

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

having count(*)>1;

And ther's list of Street_Name, Street_Number, duplicate_CODE
100 of 15000 records!
So need update old code as 'NO ACTIVE'

Нема коментара: