-- -------------------------------------------------------------
-- loadCHECKnew_ch.sql:
-- This SQL script is to check the content of the loading layer
--   here: tables NEW_*, character fields only.
--   It is called from SQL script loadCHECK.sql.
--                                                ft, 1999.07.16
-- -------------------------------------------------------------
-- 1: no NULLS allowed in NEW_*.*_id AND NEW_*.&2
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select 
       16, 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 
       17, 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: no identical items allowed in NEW_*
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       18, 0, 'none', 'NEW_&1', 'FATDUB', 'There are '||count(&2)||' &2 = '||substr(&2,1,70)
  from  NEW_&1    group by &2     having count(&2) >1;
-- -------------------------------------------------------------
-- 4: no similar items allowed in NEW_*
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       19, 0, 'none', 'NEW_&1', 'ERRDUB', '&2='||&2 ||': in this table is another item of same content (case insensitive)'
  from  NEW_&1 n  
 where  upper(&2) IN (select upper(&2) from NEW_&1 where &1._id <> n.&1._id)
   and     &2 not IN (select       &2  from NEW_&1 where &1._id <> n.&1._id)
        and &1._id is not NULL;
-- -------------------------------------------------------------
-- 5: no identical items allowed in the original LOV
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       20, 0, 'none', 'NEW_&1' ,'FATDUB','&2='||&2||' found in table CERA.&1'
  from  NEW_&1   where &2 IN (select  &2  from &1);
-- -------------------------------------------------------------
-- 6: no similar items allowed in the original LOV
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       21, 0, 'none', 'NEW_&1','ERRDUB','&2='    ||&2  ||': item of the same content (case insensitive) found in table CERA.&1'
  from  NEW_&1   where upper(&2) IN (select upper(&2) from &1)
                   and   &2 not  IN (select       &2  from &1);
-- The END of loadCHECKnew_ch.sql
