IDS_IDT_PROSPECT_STG.SQL

The following sample SQL script can be used for incremental data load.

/*
'============================================================================'
' Need to change TBLO before executing the scripts on target database.       '
'============================================================================'
*/
SET TERMOUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET TIME OFF
SET TIMING OFF
SET ECHO OFF
SET PAUSE OFF
SET PAGESIZE 50
DROP MATERIALIZED VIEW PROSPECTS_SNAPSHOT_VIEW;
CREATE MATERIALIZED VIEW PROSPECTS_SNAPSHOT_VIEW AS
SELECT
   CON_PR_ACCT_NAME  ACCOUNT_NAME,
   CELL_PH_NUM CELLULAR_PHONE,
   CITY   CITY,
   COUNTRY COUNTRY,
   EMAIL_ADDR EMAIL_ADDRESS,
   FST_NAME || ' ' || LAST_NAME  NAME,
   HOME_PH_NUM HOME_PHONE,
   MID_NAME MIDDLE_NAME,
   ZIPCODE POSTAL_CODE,
   SOC_SECURITY_NUM SOCIAL_SECURITY_NUMBER,
   STATE STATE,
   ADDR  STREETADDRESS,
   ADDR_LINE_2  ADDRESS_LINE2,
   WORK_PH_NUM WORK_PHONE,
   ROW_ID PROSPECT_ID,
   FLOOR((ROWNUM-1)/&BATCH_SIZE)+1 BATCH_NUM
FROM
   dbo.S_PRSP_CONTACT T2
/
SELECT '============================================================================' 
|| CHR(10) ||
       ' REPORT ON PROSPECTS SNAPSHOT' || CHR(10) ||
       '============================================================================' 
|| CHR(10)  "  "
       FROM DUAL
/
SELECT BATCH_NUM BATCH, COUNT(*)  "NUMBER OF RECORDS"
FROM PROSPECTS_SNAPSHOT_VIEW
GROUP BY BATCH_NUM
ORDER BY BATCH_NUM
/