create temp table tmp_costage_dim_studiengang (tid serial,
    tid_orig integer,
    st_studiengang_nr integer,
    studiengangs_identifikator character varying(255),
    studiengangs_bezeichnung character varying(255),
    st_absz_nr integer,
    st_skz_nr integer,
    fachkennzeichen character varying(255),
    st_std_art_nr integer,
    studiengangs_typ_name character varying(255),
    studiengangs_typ_kb character varying(255),
    studientyp_name character varying(255),
    studientyp_kb character varying(10),
    stp_stp_nr integer,
    stuart character(10),
    stufrm character(10),
    pversion character varying(255),
    rsz integer,
    skz_key character(10),
    skz_name character varying(255),
    unikey character varying(255),
    fakultaet_key character varying(255),
    abschluss_code character varying(255),
    abschluss_name character varying(255),
    fakultaet_kurztext character varying(255),
    fakultaet_drucktext character varying(255),
    stort_nr character varying(4),
    stort_name character varying(255),
    ist_neu smallint
);





insert into tmp_costage_dim_studiengang(
st_studiengang_nr,
       studiengangs_identifikator,
       studiengangs_bezeichnung,
       st_absz_nr,
       st_skz_nr,
       fachkennzeichen,
       st_std_art_nr,
       studiengangs_typ_name,
       studiengangs_typ_kb,
       studientyp_name,
       studientyp_kb,
       stp_stp_nr,
       stuart,
       stufrm,
       pversion,
       rsz,
       skz_key,
       skz_name,
       unikey,
       fakultaet_key,
       abschluss_code,
       abschluss_name,
       fakultaet_kurztext,
       fakultaet_drucktext,
       stort_nr,
       stort_name,
       ist_neu)

SELECT  G.st_studiengang_nr,
substring(G.studiengangs_identifikator from 1 for 255) as studiengangs_identifikator,
substring(G.studiengangs_bezeichnung from 1 for 255) as studiengangs_bezeichnung,
G.st_absz_nr,
G.st_skz_nr,
G.fachkennzeichen,
G.st_std_art_nr,
G.studiengangs_typ_name,
G.studiengangs_typ_kb,
G.studientyp_name,
G.studientyp_kb,
G.stp_stp_nr,
--G.abschluss,
G.stuart,
G.stufrm,
G.pversion,
G.rsz,
SKZ.skz_key,
SKZ.skz_name,
SKZ.unikey,
SKZ.fakultaet_key,
A.code as abschluss_code,
A.name as abschluss_name,
null::varchar(255) as fakultaet_kurztext,
null::varchar(255) as fakultaet_drucktext,
G.stort_nr,
G.stort_name,
0
FROM costage_st_studiengaenge G left outer join costage_st_studienkennzahlen SKZ
on ( SKZ.st_skz_nr=G.st_skz_nr)
left outer join costage_st_abschlussziele_lokal A on (A.st_absz_nr=G.st_absz_nr);

update tmp_costage_dim_studiengang set tid_orig=D.tid
from costage_dim_studiengang D
where D.st_studiengang_nr=tmp_costage_dim_studiengang.st_studiengang_nr;

update tmp_costage_dim_studiengang set ist_neu=1
where tid_orig is null;


create temp table tmp_hilf(
maxtid integer);

insert into tmp_hilf select max(tid) from costage_dim_studiengang;
update tmp_hilf set maxtid=0 where maxtid is null;

update tmp_costage_dim_studiengang set tid_orig=tid+(select maxtid from tmp_hilf)
where ist_neu=1;

drop table tmp_hilf;

truncate table costage_dim_studiengang;

insert into costage_dim_studiengang(tid,
st_studiengang_nr,
       studiengangs_identifikator,
       studiengangs_bezeichnung,
       st_absz_nr,
       st_skz_nr,
       fachkennzeichen,
       st_std_art_nr,
       studiengangs_typ_name,
       studiengangs_typ_kb,
       studientyp_name,
       studientyp_kb,
       stp_stp_nr,
       stuart,
       stufrm,
       pversion,
       rsz,
       skz_key,
       skz_name,
       unikey,
       fakultaet_key,
       abschluss_code,
       abschluss_name,
       fakultaet_kurztext,
       fakultaet_drucktext,
       stort_nr,
       stort_name)
select tid_orig,
st_studiengang_nr,
       studiengangs_identifikator,
       studiengangs_bezeichnung,
       st_absz_nr,
       st_skz_nr,
       fachkennzeichen,
       st_std_art_nr,
       studiengangs_typ_name,
       studiengangs_typ_kb,
       studientyp_name,
       studientyp_kb,
       stp_stp_nr,
       stuart,
       stufrm,
       pversion,
       rsz,
       skz_key,
       skz_name,
       unikey,
       fakultaet_key,
       abschluss_code,
       abschluss_name,
       fakultaet_kurztext,
       fakultaet_drucktext,
       stort_nr,
       stort_name
       from tmp_costage_dim_studiengang;
       
update costage_dim_studiengang set fakultaet_kurztext=F.fakultaet_bezeichnung,
fakultaet_drucktext=F.fakultaet_name
from costage_st_fakultaeten F
where F.fakultaet_key=costage_dim_studiengang.fakultaet_key
and F.unikey=costage_dim_studiengang.unikey;


