-- Test data script for Cohort Explorer v1.0

prompt Delete Cohort Explorer v1.0 Sample Test Data from HDWF
prompt Created on Friday, July 08, 2011
set feedback off
set define off

/*

WARNING: This script deletes all the data from multiple HDWF tables. You must run this script before running the associated insert script

WARNING: This script does not COMMIT by default. If you want to commit DELETE DML statements, issue the COMMIT command explicitly after running the Delete script.
*/


prompt Deleting HDM_SUBST_HX...
delete from HDM_SUBST_HX;
prompt Deleting HDM_SUBADMN...
delete from HDM_SUBADMN;
prompt Deleting HDM_RELTD_INTVN...
delete from HDM_RELTD_INTVN;
prompt Deleting HDM_PT_HX...
delete from HDM_PT_HX;
prompt Deleting HDM_PRTY_ADDR...
delete from HDM_PRTY_ADDR;
prompt Deleting HDM_LOC...
delete from HDM_LOC;
prompt Deleting HDM_INTVN_SUBST...
delete from HDM_INTVN_SUBST;
prompt Deleting HDM_INTVN_OBSV...
delete from HDM_INTVN_OBSV;
prompt Deleting HDM_OBSV...
delete from HDM_OBSV;
prompt Deleting HDM_SPCMN...
delete from HDM_SPCMN;
prompt Deleting HDM_INTVN...
delete from HDM_INTVN;
prompt Deleting HDM_IND_PRTY_RC...
delete from HDM_IND_PRTY_RC;
prompt Deleting HDM_IND_PRTY_ETHN...
delete from HDM_IND_PRTY_ETHN;
prompt Deleting HDM_CNSNT...
delete from HDM_CNSNT;
prompt Deleting HDM_CNRN_ANATSITE...
delete from HDM_CNRN_ANATSITE;
prompt Deleting HDM_CNRN...
delete from HDM_CNRN;
prompt Deleting HDM_PT...
delete from HDM_PT;
prompt Deleting HDM_IND_PRTY...
delete from HDM_IND_PRTY;
prompt Deleting HDM_PRTY...
delete from HDM_PRTY;
prompt Deleting HDM_CD_REPOSITORY_CD_TYP...
delete from HDM_CD_REPOSITORY_CD_TYP;
prompt Deleting HDM_CD_TYP...
delete from HDM_CD_TYP;
prompt Deleting HDM_CD_HIER...
delete from HDM_CD_HIER;
prompt Deleting HDM_CD_REPOSITORY...
delete from HDM_CD_REPOSITORY;
prompt Deleting HDM_CD_SYS...
delete from HDM_CD_SYS;

set feedback on
set define on
prompt Done deleting data.

set pagesize 999

select * from (
  select
    (select count(*) from hdm_cd_sys              ) as hdm_cd_sys,
    (select count(*) from hdm_cd_typ              ) as hdm_cd_typ,
    (select count(*) from hdm_cd_repository       ) as hdm_cd_repository,
    (select count(*) from hdm_cd_repository_cd_typ) as hdm_cd_repository_cd_typ,
    (select count(*) from hdm_cd_hier             ) as hdm_cd_hier,
    (select count(*) from hdm_prty                ) as hdm_prty,
    (select count(*) from hdm_ind_prty            ) as hdm_ind_prty,
    (select count(*) from hdm_ind_prty_rc         ) as hdm_ind_prty_rc,
    (select count(*) from hdm_ind_prty_ethn       ) as hdm_ind_prty_ethn,
    (select count(*) from hdm_loc                 ) as hdm_loc,
    (select count(*) from hdm_prty_addr           ) as hdm_prty_addr,
    (select count(*) from hdm_pt                  ) as hdm_pt,
    (select count(*) from hdm_cnsnt               ) as hdm_cnsnt,
    (select count(*) from hdm_spcmn               ) as hdm_spcmn,
    (select count(*) from hdm_pt_hx               ) as hdm_pt_hx,
    (select count(*) from hdm_subst_hx            ) as hdm_subst_hx,
    (select count(*) from hdm_cnrn                ) as hdm_cnrn,
    (select count(*) from hdm_cnrn_anatsite       ) as hdm_cnrn_anatsite,
    (select count(*) from hdm_intvn               ) as hdm_intvn,
    (select count(*) from hdm_subadmn             ) as hdm_subadmn,
    (select count(*) from hdm_intvn_subst         ) as hdm_intvn_subst,
    (select count(*) from hdm_obsv                ) as hdm_obsv,
    (select count(*) from hdm_intvn_obsv          ) as hdm_intvn_obsv,
    (select count(*) from hdm_reltd_intvn         ) as hdm_reltd_intvn
  from dual)
unpivot (recs for "TABLE" in (hdm_cd_sys, hdm_cd_typ, hdm_cd_repository, hdm_cd_repository_cd_typ, hdm_cd_hier,
                              hdm_prty, hdm_ind_prty, hdm_ind_prty_rc, hdm_ind_prty_ethn, hdm_loc, hdm_prty_addr, hdm_pt,
                              hdm_cnsnt, hdm_spcmn, hdm_pt_hx, hdm_subst_hx, hdm_cnrn, hdm_cnrn_anatsite,
                              hdm_intvn, hdm_subadmn, hdm_intvn_subst, hdm_obsv, hdm_intvn_obsv, hdm_reltd_intvn)
);

prompt This script does not COMMIT by default.
prompt If you want to commit the data then perform the COMMIT manually.
--commit;