MyTetra Share
Делитесь знаниями!
result beta
Время создания: 05.11.2017 00:39
Раздел: Ariadna home - ЛО - Токсово - Обновление участка
Запись: xintrea/mytetra_db_mcold/master/base/1509831541x6vtuot9c3/text.html на raw.githubusercontent.com

-- 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;


 
MyTetra Share v.0.60
Яндекс индекс цитирования