Category=coptot - Database Maintenance
Name=10.2. Update SMR
Description=Grabs data from INTF SMR and update it into the data model

#OutputLevel=3

Heading=Update Document table data from SMR table data

ImportSQL=select * from (select (case when smr like '%sa%' then 620000 when smr like '%bo%' then 600000 when smr like '%fa%' then 631000 when smr like '%mae%' then 632000 when smr like '%ac%' then 633000 when smr like '%cv%' then 634000 when smr like '%cw%' then 635000 when smr like '%ly%' then 636000 end) + cast(substring_index(substring_index(substring_index(substring_index(smr,' ',-1), 'L', 1), 'v', 1),'}',1) as unsigned) as DOCUMENTID, replace(replace(smr, '\\{', ''), '}', '') GANUMBER, Blattzahl LEAVESDESCRIPTION, Zzorg LINECOUNTDESCRIPTION, Blattgroesse HEIGHTDESCRIPTION, Blattgroesse2 WIDTHDESCRIPTION from smr.synopsis_manuscript) t where exists (select 1 from DOCUMENT d where d.DOCUMENTID=t.DOCUMENTID AND d.LEAVESDESCRIPTION<>t.LEAVESDESCRIPTION) order by DOCUMENTID;|UPDATE DOCUMENT set LEAVESDESCRIPTION='{2}' WHERE DOCUMENTID={0} AND (LEAVESDESCRIPTION is null or LEAVESDESCRIPTION = '');
ImportSQL2=select * from (select (case when smr like '%sa%' then 620000 when smr like '%bo%' then 600000 when smr like '%fa%' then 631000 when smr like '%mae%' then 632000 when smr like '%ac%' then 633000 when smr like '%cv%' then 634000 when smr like '%cw%' then 635000 when smr like '%ly%' then 636000 end) + cast(substring_index(substring_index(substring_index(substring_index(smr,' ',-1), 'L', 1), 'v', 1),'}',1) as unsigned) as DOCUMENTID, replace(replace(smr, '\\{', ''), '}', '') GANUMBER, Blattzahl LEAVESDESCRIPTION, Zzorg LINECOUNTDESCRIPTION, Blattgroesse HEIGHTDESCRIPTION, Blattgroesse2 WIDTHDESCRIPTION from smr.synopsis_manuscript) t where exists (select 1 from DOCUMENT d where d.DOCUMENTID=t.DOCUMENTID AND d.LINECOUNTDESCRIPTION<>t.LINECOUNTDESCRIPTION) order by DOCUMENTID;|UPDATE DOCUMENT set LINECOUNTDESCRIPTION='{3}' WHERE DOCUMENTID={0} AND (LINECOUNTDESCRIPTION is null or LINECOUNTDESCRIPTION = '');
ImportSQL3=select * from (select (case when smr like '%sa%' then 620000 when smr like '%bo%' then 600000 when smr like '%fa%' then 631000 when smr like '%mae%' then 632000 when smr like '%ac%' then 633000 when smr like '%cv%' then 634000 when smr like '%cw%' then 635000 when smr like '%ly%' then 636000 end) + cast(substring_index(substring_index(substring_index(substring_index(smr,' ',-1), 'L', 1), 'v', 1),'}',1) as unsigned) as DOCUMENTID, replace(replace(smr, '\\{', ''), '}', '') GANUMBER, Blattzahl LEAVESDESCRIPTION, Zzorg LINECOUNTDESCRIPTION, Blattgroesse HEIGHTDESCRIPTION, Blattgroesse2 WIDTHDESCRIPTION from smr.synopsis_manuscript) t where exists (select 1 from DOCUMENT d where d.DOCUMENTID=t.DOCUMENTID AND d.HEIGHTDESCRIPTION<>t.HEIGHTDESCRIPTION) order by DOCUMENTID;|UPDATE DOCUMENT set HEIGHTDESCRIPTION='{4}' WHERE DOCUMENTID={0} AND (HEIGHTDESCRIPTION is null or HEIGHTDESCRIPTION = '');
ImportSQL4=select * from (select (case when smr like '%sa%' then 620000 when smr like '%bo%' then 600000 when smr like '%fa%' then 631000 when smr like '%mae%' then 632000 when smr like '%ac%' then 633000 when smr like '%cv%' then 634000 when smr like '%cw%' then 635000 when smr like '%ly%' then 636000 end) + cast(substring_index(substring_index(substring_index(substring_index(smr,' ',-1), 'L', 1), 'v', 1),'}',1) as unsigned) as DOCUMENTID, replace(replace(smr, '\\{', ''), '}', '') GANUMBER, Blattzahl LEAVESDESCRIPTION, Zzorg LINECOUNTDESCRIPTION, Blattgroesse HEIGHTDESCRIPTION, Blattgroesse2 WIDTHDESCRIPTION from smr.synopsis_manuscript) t where exists (select 1 from DOCUMENT d where d.DOCUMENTID=t.DOCUMENTID AND d.HEIGHTDESCRIPTION<>t.HEIGHTDESCRIPTION) order by DOCUMENTID;|UPDATE DOCUMENT set WIDTHDESCRIPTION='{5}' WHERE DOCUMENTID={0} AND (WIDTHDESCRIPTION is null or WIDTHDESCRIPTION = '');

