Advertisement
Guest User

Untitled

a guest
Nov 19th, 2019
154
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.39 KB | None | 0 0
  1. -----------------------------------------------------------------------
  2. -- DDL-Statement generation 2019-11-19
  3. -----------------------------------------------------------------------
  4. -- drop the old view:
  5. -----------------------------------------------------------------------
  6. drop view filter.ERGEBNISERFASSUNG;
  7. -----------------------------------------------------------------------
  8. -- create the new view:
  9. -----------------------------------------------------------------------
  10. create view Filter.Ergebniserfassung as
  11. select
  12. pez.oid as Oid,
  13. pr.probe as Probe,
  14. pr.nahmedatum as Probenahmedatum,
  15. pr.registrierdatum as Probenregistrierdatum,
  16. pr.typ AS Probentyp,
  17. pr.art as Probenart,
  18. pr.status as Probenstatus,
  19. pr.prioritaet as Probenprioritaet,
  20. pr.reserve4 as Wetter_Nahmetag,
  21. pr.reserve5 as Proben_Id,
  22. pr.reserve6 as Probenbezeichnung,
  23. case when przu.c_sw_parallelprobe = 1 then 'ja' else 'nein' end as Parallelprobe_An_Kunde,
  24. case when przu.c_sw_stoerfall = 1 then 'ja' else 'nein' end as Stoerfall,
  25. case when przu.c_sw_sonderprobennahme = 1 then 'ja' else 'nein' end as Sonderprobenahme,
  26. case when przu.c_sw_versendetanfremdlabor = 1 then 'ja' else 'nein' end as An_Fremdlabor_Versendet,
  27. case when przu.c_sw_probennahmenichtmoeglich = 1 then 'nein' else 'ja' end as Probenahme_Moeglich,
  28. an.analyse as Analyse,
  29. an.name as Analysename,
  30. an.sw_fremduntersuchung as Fremdlaboranalyse,
  31. er.ergebnis as Ergebnis,
  32. er.name as Ergebnisname,
  33. er.art as Ergebnisart,
  34. er.typ as Ergebnistyp,
  35. pez.status as Ergebnisstatus,
  36. ew.wiederholung as Messwiederholung,
  37. ppl.pruefplan as Pruefplan,
  38. ppl.name as Pruefplan_Name,
  39. pd.produkt as Medium,
  40. pd.name as Mediumname,
  41. kd.kunde as Auftraggeber,
  42. kd.name as Auftraggebername,
  43. org.organisation as Probenstelle,
  44. org.name as Probenstellenname,
  45. hs.hauptstelle as Hauptstelle,
  46. hs.name as Hauptstellenname,
  47. us.unterstelle as Unterstelle,
  48. us.name as Unterstellenname,
  49. us.reserve3 as Schachtnummer,
  50. angr.gruppe as Analysengruppe,
  51. angr.name as Analysengruppenname
  52. from dialims.proben_erg_zuo pez
  53. left outer
  54. join dialims.proben pr on pr.oid = pez.probe_oid
  55. left outer
  56. join dialims.analysen an on an.oid = pez.analyse_oid
  57. left outer
  58. join dialims.ergebnisse er on er.oid = pez.ergebnis_oid
  59. left outer
  60. join dialims.ergebniswerte ew on ew.proben_erg_zuo_oid = pez.oid
  61. left outer
  62. join dialims.pp_p_zuo ppp on ppp.probe_oid = pr.oid
  63. left outer
  64. join dialims.pruefplaene ppl on ppl.oid = ppp.pruefplan_oid
  65. left outer
  66. join dialims.produkte pd on pd.oid = pr.produkt_oid
  67. left outer
  68. join dialims.kunden kd on kd.oid = pr.kunde_oid
  69. left outer
  70. join dialims.organisationen org on org.oid = pr.organisation_oid
  71. left outer
  72. join dialims.hauptstellen hs on hs.oid = pr.hauptstelle_oid
  73. left outer
  74. join dialims.unterstellen us on us.oid = pr.unterstelle_oid
  75. left outer
  76. join dialims.aag_zuo aag on aag.analyse_oid = an.oid
  77. left outer
  78. join dialims.analysegruppen angr on angr.oid = aag.gruppe_oid
  79. left outer
  80. join dialims.proben_ext przu on przu.c_oid = pr.oid ;
  81. -----------------------------------------------------------------------
  82. -- grants for the view:
  83. -----------------------------------------------------------------------
  84. grant select on filter.ERGEBNISERFASSUNG to dialims;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement