Земучастки сельхозназнач;
Площадь меньше 2га(20000кв.м)
ДатаРег после 2002г
Объекты
SELECT
O.CAD_NUM||O.OBJ_NUM "КадУслНо"
,(CASE
WHEN o.TSQ_UNITS=10 THEN O.TOTAL_SQ
WHEN o.TSQ_UNITS=20 THEN O.TOTAL_SQ*10000
END) "Площадь_КвМ"
,ADR_PRN_DESC "Адрес"
,o.CON_DESC "Описание"
,DECODE(o.DEPT_ID,o.DEPT_ID,(SELECT dept.SHORT_NAME FROM RP_DEPTS dept WHERE dept.ID=o.DEPT_ID)) "Отдел"
,DECODE(rps.TYPE_CODE,rps.TYPE_CODE,(SELECT TO_CHAR(rpv.NAME) FROM RE_PURPOSE_VOC rpv WHERE rpv.CODE=rps.TYPE_CODE)) "Назначение"
,rps.DIF_PURPOSE "НазначДоп"
FROM
RE_OBJECTS o
,RE_PURPOSES rps
WHERE
o.ID=rps.re_id
AND rps.TYPE_CODE LIKE '11____'
AND (CASE
WHEN o.TSQ_UNITS=10 THEN O.TOTAL_SQ
WHEN o.TSQ_UNITS=20 THEN O.TOTAL_SQ*10000
END)<20001
AND (SELECT COUNT(r.ID) FROM RT_RIGHTS r WHERE o.ID=r.re_id AND r.s_date>'01.01.2002')>0
Сводная
SELECT
DECODE(o.DEPT_ID,o.DEPT_ID,(SELECT dept.SHORT_NAME FROM RP_DEPTS dept WHERE dept.ID=o.DEPT_ID)) "Отдел"
,DECODE(rps.TYPE_CODE,rps.TYPE_CODE,(SELECT TO_CHAR(rpv.NAME) FROM RE_PURPOSE_VOC rpv WHERE rpv.CODE=rps.TYPE_CODE)) "Назначение"
,rps.DIF_PURPOSE "НазначДоп"
,count (o.id) "Колво"
FROM
RE_OBJECTS o
,RE_PURPOSES rps
WHERE
o.ID=rps.re_id
AND rps.TYPE_CODE LIKE '11____'
AND (CASE
WHEN o.TSQ_UNITS=10 THEN O.TOTAL_SQ
WHEN o.TSQ_UNITS=20 THEN O.TOTAL_SQ*10000
END)<20001
AND (SELECT COUNT(r.ID) FROM RT_RIGHTS r WHERE o.ID=r.re_id AND r.s_date>'01.01.2002')>0
group by o.DEPT_ID,rps.TYPE_CODE,rps.DIF_PURPOSE
Комментариев нет:
Отправить комментарий