-- -------------------------------------------------------------
-- loadCHECKnew_pt.sql:
-- This SQL script is to check the content of the loading layer
--   here: checking the _pointer
--   It is called from SQL script loadCHECK.sql.
--                                                ft, 1999.07.20
-- -------------------------------------------------------------
-- 1: no NULLs allowed in NEW_*.*_id AND NEW_*.&1_pointer
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select 
       31, 0, 'none', 'NEW_&1', 'FATNUL', '&1._id = NULL = &1._pointer found: correct and run loadCHECK again!'
  from  NEW_&1    where    &1._pointer is NULL and &1._id is NULL;
-- -------------------------------------------------------------
-- 2: no NULLs allowed in NEW_*.&1_pointer
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select 
       32, 0, 'none', 'NEW_&1', 'FATNUL', 'for &1._id=' ||&1._id ||': &1._pointer is NULL'
  from  NEW_&1    where    &1._pointer is NULL and &1._id is not NULL;
-- -------------------------------------------------------------
-- 3: pointer must exist in CERA.hierarchy-LOV (=&1)
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select 
       33, 0, 'none', 'NEW_&1', 'FATILL', 'for &1._id=' ||&1._id ||': &1._pointer not found in CERA.&1..&1._ID'
  from  NEW_&1
 where  &1._id       is not NULL
   and  &1._pointer  not in (select &1._id from &1);
-- The END of loadCHECKnew_pt.sql
