-- Created on 05.11.2017 by ARIADNA
declare
-- Local variables here
n_place section.code%TYPE; --VARCHAR2(50);
v_addr VARCHAR2(1000);
CURSOR pat_addr IS
SELECT p.keyid
, a.region || ' ' || a.area || ' ' || a.city || ' ' || a.town || ' ' || a.street || ' ' || a.house || ' ' || a.corp addr
FROM patient p
, addrs a
WHERE a.patientid = p.keyid
--AND rownum < 11
;
CURSOR sec_addr IS
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.code
-- , s.text
-- , s.code
-- , sos.addr
FROM SECTION s
, sos
WHERE s.Keyid = sos.SAID
-- AND sos.addr = v_addr
AND rownum = 1;
begin
-- Test statements here
FOR rec IN pat_addr LOOP
v_addr := rec.addr;
FETCH sec_addr INTO n_place;
IF sec_addr%NOTFOUND THEN
CONTINUE;
END IF;
IF NOT length(n_place) IS NULL THEN
dbms_output.put_line(n_place);
END IF;
END LOOP;
end;
