-- -------------------------------------------------------------
-- loadCHECKload_lid.sql:
-- This SQL script is to check the content of the loading layer
--   here: the IDs that refer to LOVs
--   It is called from SQL script loadCHECK.sql.
--                                                ft, 1999.07.20
-- -------------------------------------------------------------
-- 1: no NULLS allowed in the _id column
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       10, L.entry_id, LOAD_ENTRY.entry_acronym, 'LOAD_&1', 'FATNUL', '&2._id=NULL found: correct and run loadCHECK again!'
  from  LOAD_&1 L, LOAD_ENTRY where L.&2._id is NULL 
   and  L.entry_id = LOAD_ENTRY.entry_id;
--
-- -------------------------------------------------------------
-- 2: id has to exist in the original LOV or in NEW_*
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       11, L.entry_id, LOAD_ENTRY.entry_acronym, 'LOAD_&1' ,'FATILL','&2._id='||L.&2._id||' not in table CERA.&2 nor in NEW_&2'
  from  LOAD_&1 L, LOAD_ENTRY
 where  L.&2._id not IN (select &2._id from     CERA.&2)
   and  L.&2._id not IN (select &2._id from CERA.NEW_&2) 
   and  L.entry_id = LOAD_ENTRY.entry_id;
-- The END of loadCHECKnew_lid.sql
