view_chemAll.sql 1.99 KB
Newer Older
Gerd Schachtschneider's avatar
Gerd Schachtschneider committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Fake view aus sich selbst
-- Version mpg ohne mpg_chem
CREATE OR REPLACE VIEW view_chemAll AS
 SELECT
  '1' AS tabID,
  'Fake Eintrag wenn keine chemDB' AS substanz,
  '-' AS reinheit,
  '-' AS lgk,
  '0' AS cmr,
  '-' AS cas,
  '-' AS einheit,
  'mpg' AS gruppe,
  'initial' AS bearbeiter,
  '2016-03-02 11:34:18' AS zeitstempel
;

-- Version mpg mit mpg_chem
Gerd Schachtschneider's avatar
initial  
Gerd Schachtschneider committed
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- entsprechend erweitern/anpassen, wenn mehr chemie-db's existieren
CREATE OR REPLACE VIEW view_chemAll AS
 SELECT
  mpg.tabID AS tabID,
  mpg.substanz AS substanz,
  mpg.reinheit AS reinheit,
  mpg.lgk AS lgk,
  mpg.cmr AS cmr,
  mpg.cas AS cas,
  mpg.einheit AS einheit,
  'mpg' AS gruppe,
  mpg.bearbeiter AS bearbeiter,
  mpg.zeitstempel AS zeitstempel
 FROM mpidb_mpg_chem.mpi_chemstoff AS mpg
;


-- Version mpi-dcts
CREATE OR REPLACE VIEW view_chemAll AS
 SELECT
  bio.tabID AS tabID,
  bio.substanz AS substanz,
  bio.reinheit AS reinheit,
  bio.lgk AS lgk,
  bio.cmr AS cmr,
  bio.cas AS cas,
  bio.einheit AS einheit,
  'bio' AS gruppe,
  bio.bearbeiter AS bearbeiter,
  bio.zeitstempel AS zeitstempel
 FROM mpidb_bio_chem.mpi_chemstoff AS bio
UNION ALL
 SELECT
  bpe.tabID AS tabID,
  bpe.substanz AS substanz,
  bpe.reinheit AS reinheit,
  bpe.lgk AS lgk,
  bpe.cmr AS cmr,
  bpe.cas AS cas,
  bpe.einheit AS einheit,
  'bpe' AS gruppe,
  bpe.bearbeiter AS bearbeiter,
  bpe.zeitstempel AS zeitstempel
 FROM
  mpidb_bpe_chem.mpi_chemstoff AS bpe 
UNION ALL
 SELECT
  pcg.tabID AS tabID,
  pcg.substanz AS substanz,
  pcg.reinheit AS reinheit,
  pcg.lgk AS lgk,
  pcg.cmr AS cmr,
  pcg.cas AS cas,
  pcg.einheit AS einheit,
  'pcg' AS gruppe,
  pcg.bearbeiter AS bearbeiter,
  pcg.zeitstempel AS zeitstempel
 FROM
  mpidb_pcg_chem.mpi_chemstoff AS pcg 
UNION ALL
 SELECT
  pse.tabID AS tabID,
  pse.substanz AS substanz,
  pse.reinheit AS reinheit,
  pse.lgk AS lgk,
  pse.cmr AS cmr,
  pse.cas AS cas,
  pse.einheit AS einheit,
  'pse' AS gruppe,
  pse.bearbeiter AS bearbeiter,
  pse.zeitstempel AS zeitstempel
 FROM
  mpidb_pse_chem.mpi_chemstoff AS pse
;