WITH sec_add AS (select sa.keyid

, sa.sectionid said

, 1 as status

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.area_code, 1, 2), 13, '0')) as region

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.area_code, 1, 5), 13, '0')

and k.code <> rpad(substr(sa.area_code, 1, 2), 13, '0')) as area

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.city_code, 1, 8), 13, '0')

and k.code <> rpad(substr(sa.city_code, 1, 5), 13, '0')) as city

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.town_code, 1, 11), 13, '0')

and k.code <> rpad(substr(sa.town_code, 1, 8), 13, '0')) as town

, sa.street_name

, sa.house

, sa.corp

, decode(trim(sa.flat_from || sa.flat_to), null, '', sa.flat_from || ' - ' || sa.flat_to) as flats

, sa.bgndat

, sa.enddat

, sa.note

from section_addr sa

where 1=1 --sa.sectionid = p_section_id

and (sa.bgndat is null or sa.bgndat <= trunc(sysdate))

and (sa.enddat is null or sa.enddat >= trunc(sysdate))

and sa.region_id is null and sa.street_id is null

union

select sa.keyid

, sa.sectionid said

, 1 as status

, (select r.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as region

, (select a.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as area

, (select t.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and nvl(t.city_status, 1) = 1

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as city

, (select t.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and t.city_status = 2

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as town

, sa.street_name

, sa.house

, sa.corp

, decode(trim(sa.flat_from || sa.flat_to), null, '', sa.flat_from || ' - ' || sa.flat_to) as flats

, sa.bgndat

, sa.enddat

, sa.note

from section_addr sa

where 1=1 --sa.sectionid = p_section_id

and (sa.bgndat is null or sa.bgndat <= trunc(sysdate))

and (sa.enddat is null or sa.enddat >= trunc(sysdate))

and (sa.region_id is not null or sa.street_id is not null)

order by bgndat desc nulls last, region, area, city, town),

sos AS (SELECT sa.said

, sa.status

, sa.region || ' ' || sa.area || ' ' || sa.city || ' ' || sa.town || ' ' || sa.street_name || ' ' || sa.house || ' ' || sa.corp addr

FROM sec_add sa)

SELECT s.text

, s.code

, sos.*

FROM SECTION s

, sos

WHERE s.Keyid = sos.SAID