Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -----------------------------------------------------------------------
- -- DDL-Statement generation 2019-11-19
- -----------------------------------------------------------------------
- -- drop the old view:
- -----------------------------------------------------------------------
- drop view filter.ERGEBNISERFASSUNG;
- -----------------------------------------------------------------------
- -- create the new view:
- -----------------------------------------------------------------------
- create view Filter.Ergebniserfassung as
- select
- pez.oid as Oid,
- pr.probe as Probe,
- pr.nahmedatum as Probenahmedatum,
- pr.registrierdatum as Probenregistrierdatum,
- pr.typ AS Probentyp,
- pr.art as Probenart,
- pr.status as Probenstatus,
- pr.prioritaet as Probenprioritaet,
- pr.reserve4 as Wetter_Nahmetag,
- pr.reserve5 as Proben_Id,
- pr.reserve6 as Probenbezeichnung,
- case when przu.c_sw_parallelprobe = 1 then 'ja' else 'nein' end as Parallelprobe_An_Kunde,
- case when przu.c_sw_stoerfall = 1 then 'ja' else 'nein' end as Stoerfall,
- case when przu.c_sw_sonderprobennahme = 1 then 'ja' else 'nein' end as Sonderprobenahme,
- case when przu.c_sw_versendetanfremdlabor = 1 then 'ja' else 'nein' end as An_Fremdlabor_Versendet,
- case when przu.c_sw_probennahmenichtmoeglich = 1 then 'nein' else 'ja' end as Probenahme_Moeglich,
- an.analyse as Analyse,
- an.name as Analysename,
- an.sw_fremduntersuchung as Fremdlaboranalyse,
- er.ergebnis as Ergebnis,
- er.name as Ergebnisname,
- er.art as Ergebnisart,
- er.typ as Ergebnistyp,
- pez.status as Ergebnisstatus,
- ew.wiederholung as Messwiederholung,
- ppl.pruefplan as Pruefplan,
- ppl.name as Pruefplan_Name,
- pd.produkt as Medium,
- pd.name as Mediumname,
- kd.kunde as Auftraggeber,
- kd.name as Auftraggebername,
- org.organisation as Probenstelle,
- org.name as Probenstellenname,
- hs.hauptstelle as Hauptstelle,
- hs.name as Hauptstellenname,
- us.unterstelle as Unterstelle,
- us.name as Unterstellenname,
- us.reserve3 as Schachtnummer,
- angr.gruppe as Analysengruppe,
- angr.name as Analysengruppenname
- from dialims.proben_erg_zuo pez
- left outer
- join dialims.proben pr on pr.oid = pez.probe_oid
- left outer
- join dialims.analysen an on an.oid = pez.analyse_oid
- left outer
- join dialims.ergebnisse er on er.oid = pez.ergebnis_oid
- left outer
- join dialims.ergebniswerte ew on ew.proben_erg_zuo_oid = pez.oid
- left outer
- join dialims.pp_p_zuo ppp on ppp.probe_oid = pr.oid
- left outer
- join dialims.pruefplaene ppl on ppl.oid = ppp.pruefplan_oid
- left outer
- join dialims.produkte pd on pd.oid = pr.produkt_oid
- left outer
- join dialims.kunden kd on kd.oid = pr.kunde_oid
- left outer
- join dialims.organisationen org on org.oid = pr.organisation_oid
- left outer
- join dialims.hauptstellen hs on hs.oid = pr.hauptstelle_oid
- left outer
- join dialims.unterstellen us on us.oid = pr.unterstelle_oid
- left outer
- join dialims.aag_zuo aag on aag.analyse_oid = an.oid
- left outer
- join dialims.analysegruppen angr on angr.oid = aag.gruppe_oid
- left outer
- join dialims.proben_ext przu on przu.c_oid = pr.oid ;
- -----------------------------------------------------------------------
- -- grants for the view:
- -----------------------------------------------------------------------
- grant select on filter.ERGEBNISERFASSUNG to dialims;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement