-- -------------------------------------------------------------
-- loadCHECKnew_in.sql:
-- This SQL script is to check the content of the loading layer
--   here: checking, whether an item &2 in a table NEW_&1 is 
--   not NULL and exists already in a table &3 (it should).
--   It is called from SQL script loadCHECK.sql.
--                                                ft, 1999.07.20
-- -------------------------------------------------------------
-- 1: no NULLS allowed in NEW_*.*_id AND NEW_*.&2
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select 
       25, 0, 'none', 'NEW_&1', 'FATNUL', '&1._id = NULL = &2 found: correct and run loadCHECK again!'
  from  NEW_&1    where    &2 is NULL and &1._id is NULL;
-- -------------------------------------------------------------
-- 2: no NULLS allowed in NEW_*.&2
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select 
       26, 0, 'none', 'NEW_&1', 'FATNUL', 'for &1._id=' ||&1._id ||': &2 is NULL'
  from  NEW_&1    where    &2 is NULL and &1._id is not NULL;
-- -------------------------------------------------------------
-- 3: an identical item should exist
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       27, 0, 'none', 'NEW_&1', 'FATILL', 'for &1._id=' ||&1._id ||': &2 not found in CERA.&3..&2, nor in NEW_&3'
  from  NEW_&1 
 where  &2 not in (select &2 from &3 UNION select &2 from NEW_&3)
   and  &1._id is not NULL;
-- The END of loadCHECKnew_in.sql
