-- 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
-- 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
;