130 lines
3.3 KiB
SQL
130 lines
3.3 KiB
SQL
--freemarker template
|
|
<#assign teiltabellen = [
|
|
{"maskeninfo_id1":"24600", "maskeninfo_id2":"24570","sortnr":"10"},
|
|
{"maskeninfo_id1":"24600", "maskeninfo_id2":"24570","sortnr":"20"},
|
|
{"maskeninfo_id1":"24600", "maskeninfo_id2":"24570","sortnr":"30"}
|
|
] />
|
|
<#assign teiltabellen_felder = [
|
|
{"macro":"24600", "sortnr":"10","feldname":"Ausgabetabelle","value":"stud_hs1"},
|
|
{"macro":"24600", "sortnr":"10","feldname":"Zeilen","value":"hs_nr"},
|
|
{"macro":"24600", "sortnr":"10","feldname":"Spalten","value":"fgr"},
|
|
|
|
{"macro":"24600", "sortnr":"20","feldname":"Ausgabetabelle","value":"stud_rsz2"},
|
|
{"macro":"24600", "sortnr":"20","feldname":"Zeilen","value":"hs_nr"},
|
|
{"macro":"24600", "sortnr":"20","feldname":"Spalten","value":"fgr"},
|
|
|
|
{"macro":"24600", "sortnr":"30","feldname":"Ausgabetabelle","value":"absolv"},
|
|
{"macro":"24600", "sortnr":"30","feldname":"Zeilen","value":"hs_nr"},
|
|
{"macro":"24600", "sortnr":"30","feldname":"Spalten","value":"fgr"},
|
|
{"macro":"24600", "sortnr":"30","feldname":"Gewichtung","value":"1"}
|
|
|
|
] />
|
|
CREATE temp TABLE tmp_macro_masken_bez (
|
|
maskeninfo_id1 integer NOT NULL,
|
|
maskeninfo_id2 integer NOT NULL,
|
|
active integer DEFAULT 1 NOT NULL,
|
|
sortnr smallint NOT NULL
|
|
);
|
|
CREATE temp TABLE tmp_macro_feld_wert (
|
|
macro integer NOT NULL,
|
|
sortnr integer NOT NULL,
|
|
feldname character(255) NOT NULL,
|
|
alias character(255) NOT NULL,
|
|
value character(255),
|
|
value_caption character(255),
|
|
feldstand character(255),
|
|
feldsicht character(255),
|
|
active integer DEFAULT 1 NOT NULL
|
|
);
|
|
|
|
|
|
--Alte Version umhängen:
|
|
delete from macro_feld_wert where macro=32580;
|
|
delete from macro_masken_bez where maskeninfo_id1=32580;
|
|
|
|
|
|
|
|
<#foreach tab in teiltabellen>
|
|
|
|
insert into tmp_macro_masken_bez(maskeninfo_id1,
|
|
maskeninfo_id2,
|
|
active,
|
|
sortnr)
|
|
select ${tab.maskeninfo_id1},--maskeninfo_id1,
|
|
${tab.maskeninfo_id2},--maskeninfo_id2,
|
|
1,--active,
|
|
${tab.sortnr} --sortnr,
|
|
from xdummy;
|
|
|
|
|
|
</#foreach>
|
|
|
|
<#foreach feld in teiltabellen_felder>
|
|
|
|
insert into tmp_macro_feld_wert
|
|
( macro,
|
|
sortnr,
|
|
feldname,
|
|
alias,
|
|
value,
|
|
value_caption,
|
|
feldstand,
|
|
feldsicht,
|
|
active)
|
|
SELECT ${feld.macro}, --macro
|
|
${feld.sortnr},--sortnr
|
|
'${feld.feldname}',--feldname,
|
|
'',--alias,
|
|
'${feld.value}', --value
|
|
'${feld.value}',--value_caption,
|
|
'', --feldstand,
|
|
'',--feldsicht,
|
|
1 --active
|
|
FROM xdummy;
|
|
</#foreach>
|
|
|
|
delete from macro_masken_bez where maskeninfo_id1 in (select maskeninfo_id1 from tmp_macro_masken_bez);
|
|
|
|
|
|
insert into macro_masken_bez
|
|
(maskeninfo_id1,
|
|
maskeninfo_id2,
|
|
active,
|
|
sortnr)
|
|
select maskeninfo_id1,
|
|
maskeninfo_id2,
|
|
active,
|
|
sortnr
|
|
FROM tmp_macro_masken_bez;
|
|
|
|
|
|
delete from macro_feld_wert where macro in (select macro from tmp_macro_feld_wert);
|
|
|
|
insert into macro_feld_wert
|
|
( macro,
|
|
sortnr,
|
|
feldname,
|
|
alias,
|
|
value,
|
|
value_caption,
|
|
feldstand,
|
|
feldsicht,
|
|
active)
|
|
SELECT macro,
|
|
sortnr,
|
|
feldname,
|
|
alias,
|
|
value,
|
|
value_caption,
|
|
feldstand,
|
|
feldsicht,
|
|
active
|
|
FROM tmp_macro_feld_wert;
|
|
|
|
drop table tmp_macro_feld_wert;
|
|
|
|
drop table tmp_macro_masken_bez;
|
|
|
|
|
|
|