create temp table tmp_kenn_bland ( apnr varchar(255), druck varchar(255), astat integer);

INSERT INTO tmp_kenn_bland (apnr,druck,astat) 
VALUES
  ('BA','Bayern',9),
  ('BB','Brandenburg',12),
  ('BE','Berlin',11),
  ('BR','Bremen',4),
  ('BW','Baden-Württemberg',8),
  ('HA','Hamburg',2),
  ('HE','Hessen',6),
  ('MV','Mecklenburg-Vorpommern',13),
  ('NI','Niedersachsen',3),
  ('NW','Nordrhein-Westfalen',5),
  ('RP','Rheinland-Pfalz',7),
  ('SA','Saarland',10),
  ('SH','Schleswig-Holstein',1),
  ('SN','Sachsen',14),
  ('ST','Sachsen-Anhalt',15),
  ('TH','Thüringen',16);

create temp table tmp_kenn_bland_hs (bland integer);

insert into tmp_kenn_bland_hs (bland)
select astat from tmp_kenn_bland where apnr in (select K.bland from kenn_hochschulen K, hochschulinfo H where H.hs_nr=K.hs_nr);

SELECT schluessel,
       land,
       null::char(10) as keybund,
       max(nvl(gueltig_bis,30001)) as gueltig_bis
   into temp tmp_stg
FROM k_astat_studienfach_land
where land=(select bland from tmp_kenn_bland_hs)
group by 1,2,3;

update tmp_stg set keybund=(select distinct L.keybund
from k_astat_studienfach_land L
where L.schluessel=tmp_stg.schluessel
and L.land=tmp_stg.land
and (L.gueltig_bis=tmp_stg.gueltig_bis or (L.gueltig_bis is null and tmp_stg.gueltig_bis =30001))
);


update kenn_antr_aggr set stg=(select K.keybund from tmp_stg K where (kenn_antr_aggr.stg=K.schluessel or kenn_antr_aggr.stg='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_antr_aggr.stg=K.schluessel or kenn_antr_aggr.stg='0' || K.schluessel);

update kenn_lab_aggr_astat set stg=(select K.keybund from tmp_stg K where (kenn_lab_aggr_astat.stg=K.schluessel or kenn_lab_aggr_astat.stg='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_lab_aggr_astat.stg=K.schluessel or kenn_lab_aggr_astat.stg='0' || K.schluessel);

update kenn_sos_lab set stg=(select K.keybund from tmp_stg K where (kenn_sos_lab.stg=K.schluessel or kenn_sos_lab.stg='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_sos_lab.stg=K.schluessel or kenn_sos_lab.stg='0' || K.schluessel);

update kenn_sos_stg set stg=(select K.keybund from tmp_stg K where (kenn_sos_stg.stg=K.schluessel or kenn_sos_stg.stg='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_sos_stg.stg=K.schluessel or kenn_sos_stg.stg='0' || K.schluessel);

update kenn_stg_aggr set stg=(select K.keybund from tmp_stg K where (kenn_stg_aggr.stg=K.schluessel or kenn_stg_aggr.stg='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_stg_aggr.stg=K.schluessel or kenn_stg_aggr.stg='0' || K.schluessel);

update kenn_stg_aggr_astat set stg=(select K.keybund from tmp_stg K where (kenn_stg_aggr_astat.stg=K.schluessel or kenn_stg_aggr_astat.stg='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_stg_aggr_astat.stg=K.schluessel or kenn_stg_aggr_astat.stg='0' || K.schluessel);

update kenn_stg_int set astat=(select K.keybund from tmp_stg K where (kenn_stg_int.astat=K.schluessel or kenn_stg_int.astat='0' || K.schluessel))
where 1=(select count(*) from tmp_stg K where kenn_stg_int.astat=K.schluessel or kenn_stg_int.astat='0' || K.schluessel);


update kenn_antr_aggr set stg='0' || stg where length(trim(stg))=3;
update kenn_lab_aggr_astat set stg='0' || stg where length(trim(stg))=3;
update kenn_sos_lab set stg='0' || stg where length(trim(stg))=3;
update kenn_sos_stg set stg='0' || stg where length(trim(stg))=3;
update kenn_stg_aggr set stg='0' || stg where length(trim(stg))=3;
update kenn_stg_aggr_astat set stg='0' || stg where length(trim(stg))=3;
update kenn_stg_int set astat='0' || astat where length(trim(astat))=3;




--Korrektur Fächergruppe:
select distinct stg, 
null::char(19) as sb,
null::char(10) AS fgr
into temp tmp_stg_aggr_astat
from kenn_stg_aggr_astat;

insert into tmp_stg_aggr_astat
select distinct stg, 
null::char(19) as sb,
null::char(10) AS fgr
from kenn_lab_aggr_astat;

update tmp_stg_aggr_astat set sb=(select A.parent from kenn_stg_astat A
where A.astat=tmp_stg_aggr_astat.stg);
update tmp_stg_aggr_astat set fgr=(select A.parent from kenn_stg_astat A
where A.astat=tmp_stg_aggr_astat.sb);

update tmp_stg_aggr_astat set fgr=(select A.apnr from kenn_dim_fgr A
where A.fg_studierende=tmp_stg_aggr_astat.fgr);

update tmp_stg_aggr_astat set fgr='U' where fgr is null;

update kenn_stg_aggr_astat set fgr=(select min(T.fgr)
from tmp_stg_aggr_astat T
where T.stg=kenn_stg_aggr_astat.stg);

update kenn_lab_aggr_astat set fgr=(select min(T.fgr)
from tmp_stg_aggr_astat T
where T.stg=kenn_lab_aggr_astat.stg);

drop table tmp_stg_aggr_astat;
drop table tmp_kenn_bland;
drop table tmp_kenn_bland_hs;
drop table tmp_stg;