120 lines
2.9 KiB
SQL
120 lines
2.9 KiB
SQL
--
|
|
-- $Id$
|
|
--
|
|
-- <<Opschoning dubbele gebouwen onder district Oost Brabant>>
|
|
|
|
DEFINE thisfile = 'RABO#54852.SQL'
|
|
DEFINE dbuser = '^RABO'
|
|
|
|
SET ECHO ON
|
|
SET DEFINE ON
|
|
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
|
|
WHENEVER SQLERROR EXIT;
|
|
SELECT adm.scriptspoolfile('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;
|
|
WHENEVER SQLERROR CONTINUE;
|
|
SPOOL &fcltlogfile
|
|
SET DEFINE OFF
|
|
|
|
------ payload begin ------
|
|
|
|
-- Vanaf ruimte_key naar regio/kring alle record verwijderen voor regiokey 61 (=oost brabant)
|
|
|
|
DELETE
|
|
alg_ruimte aru2
|
|
WHERE aru2.alg_ruimte_key IN
|
|
(SELECT aru.alg_ruimte_key FROM
|
|
alg_regio ar,
|
|
alg_district ad,
|
|
alg_locatie al,
|
|
alg_gebouw ag,
|
|
alg_verdieping av,
|
|
alg_ruimte aru
|
|
WHERE
|
|
ar.alg_regio_key = ad.alg_regio_key
|
|
and ad.alg_district_key = al.alg_district_key
|
|
and al.alg_locatie_key = ag.alg_locatie_key
|
|
and ag.alg_gebouw_key = av.alg_gebouw_key
|
|
and av.alg_verdieping_key = aru.alg_verdieping_key
|
|
and ar.alg_regio_key in (61)
|
|
) ;
|
|
|
|
|
|
DELETE
|
|
alg_verdieping av2
|
|
WHERE av2.alg_verdieping_key IN
|
|
(SELECT av.alg_verdieping_key FROM
|
|
alg_regio ar,
|
|
alg_district ad,
|
|
alg_locatie al,
|
|
alg_gebouw ag,
|
|
alg_verdieping av
|
|
WHERE
|
|
ar.alg_regio_key = ad.alg_regio_key
|
|
and ad.alg_district_key = al.alg_district_key
|
|
and al.alg_locatie_key = ag.alg_locatie_key
|
|
and ag.alg_gebouw_key = av.alg_gebouw_key
|
|
and ar.alg_regio_key in (61)
|
|
) ;
|
|
|
|
DELETE
|
|
alg_gebouw ag2
|
|
WHERE ag2.alg_gebouw_key IN
|
|
(SELECT ag.alg_gebouw_key FROM
|
|
alg_regio ar,
|
|
alg_district ad,
|
|
alg_locatie al,
|
|
alg_gebouw ag
|
|
WHERE
|
|
ar.alg_regio_key = ad.alg_regio_key
|
|
and ad.alg_district_key = al.alg_district_key
|
|
and al.alg_locatie_key = ag.alg_locatie_key
|
|
and ar.alg_regio_key in (61)
|
|
) ;
|
|
|
|
|
|
DELETE
|
|
alg_locatie al2
|
|
WHERE al2.alg_locatie_key IN
|
|
(SELECT al.alg_locatie_key FROM
|
|
alg_regio ar,
|
|
alg_district ad,
|
|
alg_locatie al
|
|
WHERE
|
|
ar.alg_regio_key = ad.alg_regio_key
|
|
and ad.alg_district_key = al.alg_district_key
|
|
and ar.alg_regio_key in (61)
|
|
) ;
|
|
|
|
DELETE
|
|
alg_district ad2
|
|
WHERE ad2.alg_district_key IN
|
|
(SELECT ad.alg_district_key FROM
|
|
alg_regio ar,
|
|
alg_district ad
|
|
WHERE
|
|
ar.alg_regio_key = ad.alg_regio_key
|
|
and ar.alg_regio_key in (61)
|
|
) ;
|
|
|
|
DELETE
|
|
alg_regio ar2
|
|
WHERE ar2.alg_regio_key IN
|
|
(SELECT ar.alg_regio_key FROM
|
|
alg_regio ar
|
|
WHERE
|
|
ar.alg_regio_key in (61)
|
|
) ;
|
|
|
|
|
|
------ payload end ------
|
|
|
|
SET DEFINE OFF
|
|
BEGIN adm.systrackscriptId ('$Id$', 1); END;
|
|
/
|
|
|
|
COMMIT;
|
|
SET ECHO OFF
|
|
SPOOL OFF
|
|
SET DEFINE ON
|
|
PROMPT Logfile of this upgrade is: &fcltlogfile
|