SQL-Oracle

среда, 15 июня 2011 г.

Помещения в Абакане

SELECT
o.NAME "Наим"
--,o.ADR_DESC "Адр"
,o.ADR_PRN_DESC "АдрСв"
,r.S_DATE "Дата"
,DECODE(r.TYPE_CODE,r.TYPE_CODE,(SELECT RV.NAME FROM RT_RIGHTS_VOC rv WHERE rv.code=r.type_code)) "ТипПрава"
,r.part "Часть"
--,r.REG_NO "РегНо"
,Ent.con_desc(en.ID) "Правообладатель"
 
FROM
RE_OBJECTS o
,RT_RIGHTS r
,RT_ENT rte
,ENT_ENTITIES en
WHERE
o.ID=r.re_id
AND rte.RT_ID=r.ID
AND rte.ENT_ID = en.ID
AND o.R_TYPE=3 --часть
AND r.R_GROUP = 'П'
AND
  (
      r.e_DATE IS NULL
   OR (
        r.e_date > TO_DATE ('01.01.2011', 'dd.mm.yyyy')
        AND r.s_date < TO_DATE ('01.01.2011', 'dd.mm.yyyy')
      )
  )
AND LOWER(o.NAME) NOT LIKE '%погреб%'
AND LOWER(o.NAME) NOT LIKE '%гараж%'
AND LOWER(o.NAME) NOT LIKE '%подвал%'
AND LOWER(o.ADR_DESC) NOT LIKE '%усть-%'
    
AND LOWER(o.ADR_PRN_DESC) LIKE '%абакан%'
 
ORDER BY o.ADR_DESC

среда, 1 июня 2011 г.

Контроль создания адреса

select
ad.ID
,(SELECT c.RV_ABBREVIATION FROM CG_REF_CODES c WHERE  c.RV_LOW_VALUE= ad.R_LEVEL AND  c.RV_DOMAIN='ADR УРОВЕНЬ АДРЕСА (СПИСОК)')"УрАдр"
,AD.R_LEVEL
,AD.NAME
,RP.EMP_NAME(ad.Inserted_by) "Сотрудник"
,(SELECT  wm_concat(NAME)
      FROM adr_address_voc
START WITH adr_address_voc.ID = ad.ID
CONNECT BY adr_address_voc.ID = PRIOR adr_address_voc.par_id) "Адрес"
from 
adr_address_voc ad
where
ad.inserted BETWEEN TO_DATE ('&start#', 'dd.mm.yyyy') AND TO_DATE ('&end#', 'dd.mm.yyyy')
and AD.R_LEVEL <40
and ad.dept_id is not null

понедельник, 30 мая 2011 г.

Отчет по арендам

SELECT 
b.REG_NO "РегНо"
,(SELECT bv.NAME FROM BRG_BARGAINS_VOC bv WHERE bv.CODE=b.TYPE_CODE) "ТипСделки"
,r.S_DATE "ДатаРег"     

,en.name "Арендодатель"
,EN.S_DATE "ДатаРожд"
,'Серия:'||EN.REG_DOC_SER||' номер:'||EN.REG_DOC_NUM||' дата выдачи:'||EN.REG_DOC_DATE||' выдан:'||EN.REG_DOC_ORG "Паспорт"
,o.ADR_DESC "Адрес"     
,o.CON_DESC "ОписОбъекта"
,r.rs_s_date||' - '||r.rs_e_date "СрокАренды"
,(select wm_concat(r204.RE_PRICE||'('||r204.price_desc||')')  
  from rt_rights r204
  where
     R204.TYPE_CODE=204
     and r204.BRG_ID=b.ID 
 )"Цена"
,b.CONDITION "ОписСделки"
FROM
BRG_BARGAINS b
,RT_RIGHTS r
,RT_RIGHTS rr
,RT_RS rtrs
,RE_OBJECTS o
,doc_foundations d
,rt_ent rent
,ent_entities en
WHERE

r.BRG_ID=b.ID
AND o.ID=r.RE_ID
AND d.ID=B.DOC_ID

and rtrs.RS_ID=r.ID 
AND rtrs.RT_ID=rr.ID

and RENT.RT_ID=RR.ID
and RENT.ENT_ID=EN.ID
and EN.R_TYPE='Ф'

and r.rs_e_date > TO_DATE ('&start#', 'dd.mm.yyyy') 
and r.rs_s_date < TO_DATE ('&end#', 'dd.mm.yyyy') 
and r.e_date >TO_DATE ('&start#', 'dd.mm.yyyy') 
and b.TYPE_CODE = 9 -- Аренда
and R.TYPE_CODE = 201-- Аренда
and (lower(o.ADR_PRN_DESC) like '%алтайский район%' or lower(o.ADR_PRN_DESC) like '%абакан%')  
and (lower(o.ADR_PRN_DESC) not like '%усть-абакан%')
and (lower(o.ADR_PRN_DESC) not like '%аскизский район%')
ORDER BY b.REG_NO

К-П за 2010 год

SELECT 
b.REG_NO "РегНо"
--,r.REG_NO "РегНоRT"     
,(SELECT bv.NAME FROM BRG_BARGAINS_VOC bv WHERE bv.CODE=b.TYPE_CODE) "ТипСделки"
,r.S_DATE "ДатаРег"     
,O.TOTAL_SQ "Площадь"   
,r.RE_PRICE "Цена"
,B.PRICE "Цена2"      
,o.ADR_DESC "Адрес"     
,o.CON_DESC "ОписОбъекта"
,b.CONDITION "ОписСделки"
,d.NAME
,Rt.ent_desc(r.ID) "покупатель"
,(SELECT wm_concat(Rt.ent_desc(rr.ID))  
  FROM  
    RT_RS rtrs, RT_RIGHTS rr
  WHERE 
    rtrs.RS_ID=r.ID 
    AND rtrs.RT_ID=rr.ID
) "Продавец"
FROM
BRG_BARGAINS b
,RT_RIGHTS r
,RE_OBJECTS o
,doc_foundations d
WHERE

r.BRG_ID=b.ID
AND o.ID=r.RE_ID
AND d.ID=B.DOC_ID

AND R.S_DATE BETWEEN TO_DATE ('&start#', 'dd.mm.yyyy') AND TO_DATE ('&end#', 'dd.mm.yyyy')

and b.TYPE_CODE=1 --купля-продажа
and R.TYPE_CODE = 204-- Участие в сделке
and form_8.re_type_form(r.re_id) = 232--231-нежилое;232-жилое ;1-ЗУ 
ORDER BY b.REG_NO

воскресенье, 29 мая 2011 г.

Длинный адрес

select 
reo.ID "OBJ_ID"
,REO.CAD_NUM||reo.OBJ_NUM "КадУслНо"
,reo.adr_id "ADR_ID" 
,length(adr.create_descr(reo.adr_id)) "ДлАдр"
,adr.create_descr(reo.adr_id) "Адрес"
,reo.adr_prn_changed "isИЗМ"
,REO.INSERTED
,REO.UPDATED
from 
  re_objects reo
, adr_address_voc aav

where 
    REO.ADR_ID = AAV.ID
and length(adr.create_descr(reo.adr_id)) > 250

вторник, 19 апреля 2011 г.

Необходимо выполнить в филиалах

/*
select ADR_PRN_CHANGED from RE_objects o
*/
ALTER TRIGGER AER_RE_OBJECTS DISABLE;
ALTER TRIGGER BER_RE_OBJECTS DISABLE;
update RE_objects o set ADR_PRN_CHANGED = 'Д' ;
ALTER TRIGGER AER_RE_OBJECTS ENABLE;
ALTER TRIGGER BER_RE_OBJECTS ENABLE;

ALTER TRIGGER BER_ADR_ADDRESS_VOC DISABLE;
ALTER TRIGGER AER_ADR_ADDRESS_VOC DISABLE;
update adr_address_voc ad set ad.name=substr(ad.name,0,215) where ad.id = 12058006;
update adr_address_voc ad set ad.name=substr(ad.name,0,13) where ad.id = 34055010;
update adr_address_voc ad set ad.name=substr(ad.name,0,13) where ad.id = 41920010;
update adr_address_voc ad set ad.name=substr(ad.name,0,13) where ad.id = 173438001;
update adr_address_voc ad set ad.name=substr(ad.name,0,14) where ad.id = 54995011;
update adr_address_voc ad set ad.name=substr(ad.name,0,18) where ad.id = 215105001;
update adr_address_voc ad set ad.name=substr(ad.name,0,19) where ad.id = 63548011;
update adr_address_voc ad set ad.name=substr(ad.name,0,21) where ad.id = 41947010;
update adr_address_voc ad set ad.name=substr(ad.name,0,22) where ad.id = 237147001;
update adr_address_voc ad set ad.name=substr(ad.name,0,23) where ad.id = 173486001;
update adr_address_voc ad set ad.name=substr(ad.name,0,25) where ad.id = 174477001;
update adr_address_voc ad set ad.name=substr(ad.name,0,27) where ad.id = 172715001;
update adr_address_voc ad set ad.name=substr(ad.name,0,28) where ad.id = 174362001;
update adr_address_voc ad set ad.name=substr(ad.name,0,30) where ad.id = 187976001;
update adr_address_voc ad set ad.name=substr(ad.name,0,32) where ad.id = 52694011;
update adr_address_voc ad set ad.name=substr(ad.name,0,32) where ad.id = 186167001;
update adr_address_voc ad set ad.name=substr(ad.name,0,33) where ad.id = 52704011;
update adr_address_voc ad set ad.name=substr(ad.name,0,39) where ad.id = 48880003;
update adr_address_voc ad set ad.name=substr(ad.name,0,43) where ad.id = 173358001;
update adr_address_voc ad set ad.name=substr(ad.name,0,43) where ad.id = 224311001;
update adr_address_voc ad set ad.name=substr(ad.name,0,44) where ad.id = 246718001;
update adr_address_voc ad set ad.name=substr(ad.name,0,46) where ad.id = 196856001;
update adr_address_voc ad set ad.name=substr(ad.name,0,47) where ad.id = 174446001;
update adr_address_voc ad set ad.name=substr(ad.name,0,48) where ad.id = 172753001;
update adr_address_voc ad set ad.name=substr(ad.name,0,53) where ad.id = 172322001;
update adr_address_voc ad set ad.name=substr(ad.name,0,57) where ad.id = 65640010;
update adr_address_voc ad set ad.name=substr(ad.name,0,57) where ad.id = 219390001;
update adr_address_voc ad set ad.name=substr(ad.name,0,58) where ad.id = 219399001;
update adr_address_voc ad set ad.name=substr(ad.name,0,59) where ad.id = 172741001;
update adr_address_voc ad set ad.name=substr(ad.name,0,60) where ad.id = 171967001;
update adr_address_voc ad set ad.name=substr(ad.name,0,62) where ad.id = 56074011;
update adr_address_voc ad set ad.name=substr(ad.name,0,63) where ad.id = 48805003;
update adr_address_voc ad set ad.name=substr(ad.name,0,63) where ad.id = 203077001;
update adr_address_voc ad set ad.name=substr(ad.name,0,80) where ad.id = 146384001;
update adr_address_voc ad set ad.name=substr(ad.name,0,82) where ad.id = 41470011;
update adr_address_voc ad set ad.name=substr(ad.name,0,82) where ad.id = 67951010;
update adr_address_voc ad set ad.name=substr(ad.name,0,88) where ad.id = 210090001;
update adr_address_voc ad set ad.name=substr(ad.name,0,89) where ad.id = 169031001;
update adr_address_voc ad set ad.name=substr(ad.name,0,110) where ad.id = 163092001;
update adr_address_voc ad set ad.name=substr(ad.name,0,119) where ad.id = 165101001;
update adr_address_voc ad set ad.name=substr(ad.name,0,133) where ad.id = 42538011;
update adr_address_voc ad set ad.name=substr(ad.name,0,138) where ad.id = 174239001;
update adr_address_voc ad set ad.name=substr(ad.name,0,138) where ad.id = 237097001;
update adr_address_voc ad set ad.name=substr(ad.name,0,153) where ad.id = 250430001;
update adr_address_voc ad set ad.name=substr(ad.name,0,156) where ad.id = 194864001;
update adr_address_voc ad set ad.name=substr(ad.name,0,164) where ad.id = 49236003;
update adr_address_voc ad set ad.name=substr(ad.name,0,170) where ad.id = 91466002;
update adr_address_voc ad set ad.name=substr(ad.name,0,179) where ad.id = 183804001;
update adr_address_voc ad set ad.name=substr(ad.name,0,189) where ad.id = 134250001;
update adr_address_voc ad set ad.name=substr(ad.name,0,198) where ad.id = 239469001;
update adr_address_voc ad set ad.name=substr(ad.name,0,198) where ad.id = 240335001;
update adr_address_voc ad set ad.name=substr(ad.name,0,201) where ad.id = 75656011;
update adr_address_voc ad set ad.name=substr(ad.name,0,202) where ad.id = 189157001;
update adr_address_voc ad set ad.name=substr(ad.name,0,207) where ad.id = 173393001;
update adr_address_voc ad set ad.name=substr(ad.name,0,213) where ad.id = 41066011;
update adr_address_voc ad set ad.name=substr(ad.name,0,213) where ad.id = 165692001;
update adr_address_voc ad set ad.name=substr(ad.name,0,215) where ad.id = 48796003;
update adr_address_voc ad set ad.name=substr(ad.name,0,215) where ad.id = 48799003;
update adr_address_voc ad set ad.name=substr(ad.name,0,216) where ad.id = 49445002;
update adr_address_voc ad set ad.name=substr(ad.name,0,216) where ad.id = 89054002;
update adr_address_voc ad set ad.name=substr(ad.name,0,230) where ad.id = 242588001;

update adr_address_voc ad set ad.name='14-2-1' where ad.id = 42668009;
update adr_address_voc ad set ad.name='4-2-1' where ad.id = 42666009;
update adr_address_voc ad set ad.name='2-1-1' where ad.id = 42665009;
update adr_address_voc ad set ad.name='1-9-3б,1-9-5,1-15' where ad.id = 42664009;
update adr_address_voc ad set ad.name='42-43-73' where ad.id = 42663009;

update adr_address_voc ad set ad.name='оп149 - ТП-26-29-15,оп153 - оп153-4' where ad.id = 42898010;
update adr_address_voc ad set ad.name='оп125-4 - оп125-4-8км,оп148 - ТП-26-29-14' where ad.id = 42897010;
update adr_address_voc ad set ad.name='оп125 - ТП-15-10 (лн01),оп125 - ТП-26-29-19 (лн02)' where ad.id = 42896010;
update adr_address_voc ad set ad.name='ЗРУ-10кВ ПС"КСК" - оп35 с отп.оп26 - оп26-1км,оп110 - ТП-05' where ad.id = 42895010;

update adr_address_voc ad set ad.name='оп5 - оп19' where ad.id = 42713009;
update adr_address_voc ad set ad.name='оп4-1-1 - оп4-1-7' where ad.id = 42712009;
update adr_address_voc ad set ad.name='оп4-10 - оп4-13' where ad.id = 42711009;
update adr_address_voc ad set ad.name='оп4-8-2' where ad.id = 42708009;
update adr_address_voc ad set ad.name='оп4-1 - оп4-9' where ad.id = 42707009;
update adr_address_voc ad set ad.name='ТП 42-43-21 - оп4' where ad.id = 42706009;

ALTER TRIGGER BER_ADR_ADDRESS_VOC ENABLE;
ALTER TRIGGER AER_ADR_ADDRESS_VOC ENABLE;
commit;

после прогрузки

select * from cg_parameters cp where cp.name='MODE'; 
update cg_par_values cpv set cpv.value='Р' where cpv.cg_par_id = 8000;

понедельник, 18 апреля 2011 г.

Повторная загрузка МО (минобороны)

Отключаем CONSTRAINT UC_NAL_PMO_PERSON

select *
--delete
from
  NAL_PERSONS_MO npm
where
  (
    select
      count (npm2.ID)
    from
      NAL_PERSONS_MO npm2
    where
        npm.PERSON_ID = npm2.PERSON_ID
        and npm.REQUEST_NAME = npm2.REQUEST_NAME
        and npm.ID <> npm2.ID
  ) >0
  -- Дата последней прогрузки минус 1мин
  --and npm.PROCESSED < TO_DATE('19.04.2011 9:50:00', 'DD.MM.YYYY HH24:MI:SS')

Архив блога

среда, 15 июня 2011 г.

среда, 1 июня 2011 г.

понедельник, 30 мая 2011 г.

воскресенье, 29 мая 2011 г.

вторник, 19 апреля 2011 г.

понедельник, 18 апреля 2011 г.

среда, 15 июня 2011 г.

среда, 1 июня 2011 г.

понедельник, 30 мая 2011 г.

воскресенье, 29 мая 2011 г.

вторник, 19 апреля 2011 г.

понедельник, 18 апреля 2011 г.