Правка субъектов

select
en.id "ID"
,RP.DEPT_NAME(EN.DEPT_ID) "Филиал"
,en.r_type "Тип"
,en.name "Субъект"
,EN.CITIZEN "Страна"
,EA.ADR_DESC "АдрОпис"
,adr.create_descr(ea.adr_id) "АдрСпр" 
from
 ent_entities en
,ent_address ea

where
EN.ID=ea.id
and en.id in (select rte.ent_id from rt_ent rte)
and lower(EA.ADR_DESC) not like '%республика%хакасия%'
--and lower(adr.create_descr(ea.adr_id)) not like '%республика%хакасия%'
and lower(EA.ADR_DESC) like lower('%!!!НЕКОРРЕКТНЫЙ АДРЕС!!!%')

select
en.id "ID"
,RP.DEPT_NAME(EN.DEPT_ID) "Филиал"
,en.r_type "Тип"
,en.name "Субъект"
,EN.CITIZEN "Страна"
,EA.ADR_DESC "АдрОпис"
,adr.create_descr(ea.adr_id) "АдрСпр"
,(select AA.R_LEVEL from adr_address_voc aa where aa.id=ea.adr_id ) "АдрУр"
,length(adr.create_descr(ea.adr_id)) "АдрДл"
 
from
 ent_entities en
,ent_address ea

where
EN.ID=ea.id
and en.id in (select rte.ent_id from rt_ent rte)
and lower(EA.ADR_DESC) not like '%республика%хакасия%'
and lower(adr.create_descr(ea.adr_id)) not like '%республика%хакасия%'
--and lower(EA.ADR_DESC) like lower('%!!!НЕКОРРЕКТНЫЙ АДРЕС!!!%')

order by length(adr.create_descr(ea.adr_id))

select
--выборка субъектов с необщей верхней частью адреса aav.R_LEVEL < 40
en.id "ID"
,RP.DEPT_NAME(EN.DEPT_ID) "Филиал"
,en.r_type "Тип"
,en.name "Субъект"
,EN.CITIZEN "Страна"
,EA.ADR_DESC "АдрОпис"
,ea.adr_id
,adr.create_descr(ea.adr_id) "АдрСпр"
from
 ent_entities en
,ent_address ea
 
where
EN.ID=ea.id
and en.id in (select rte.ent_id from rt_ent rte)
and (
    SELECT 
    sum(case 
        when aav.R_LEVEL < 40 and aav.DEPT_ID is not null then 
          (case 
            when (
                    select count(eam.old_ID) 
                    from 
                        exp_voc_meanings eam
                        ,adr_address_voc a2
                    where 
                        eam.OLD_ID=aav.ID 
                        and eam.tab_name='ADR_ADDRESS_VOC'
                        and eam.NEW_ID=a2.id
                        and a2.DEPT_ID is null
                 ) = 0 then  aav.DEPT_ID
            else null
          end)
        else null
    end)
    FROM adr_address_voc aav

    START WITH aav.ID = ea.adr_id
    CONNECT BY aav.ID = PRIOR aav.par_id
) >0

--and lower(EA.ADR_DESC) not like '%республика%хакасия%'
--and lower(adr.create_descr(ea.adr_id)) not like '%республика%хакасия%'
--and lower(EA.ADR_DESC) like lower('%!!!НЕКОРРЕКТНЫЙ АДРЕС!!!%'