IDS_IDT_ACCOUNT_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
DROP MATERIALIZED VIEW ACCOUNTS_SNAPSHOT_VIEW;
CREATE MATERIALIZED VIEW ACCOUNTS_SNAPSHOT_VIEW AS
SELECT
   T2.ROW_ID   ACCOUNT_ID,
   T2.NAME   ACCOUNT_NAME,
   T3.ROW_ID   ACCOUNT_ADDR_ID,
   T3.ADDR   ADDRESS_LINE1,
   T3.ADDR_LINE_2   ADDRESS_LINE2,
   T3.COUNTRY   COUNTRY,
   T3.STATE   STATE,
   T3.CITY   CITY,
   T3.ZIPCODE   POSTAL_CODE,
   DECODE(T2.PR_BL_ADDR_ID,T3.ROW_ID,'Y','N')   PRIMARY_FLAG,
   FLOOR((ROWNUM-1)/&BATCH_SIZE)+1   BATCH_NUM
   FROM
      dbo.S_CON_ADDR T1,
      dbo.S_ORG_EXT T2,
      dbo.S_ADDR_PER T3
   WHERE
      T1.ACCNT_ID = T2.ROW_ID
   AND
      T1.ADDR_PER_ID = T3.ROW_ID
-- Comment the following line for Multiple address match option
--   AND T2.PR_BL_ADDR_ID=T3.ROW_ID
/
SELECT '============================================================================' 
|| CHR(10) ||
       '                  REPORT ON ACCOUNTS SNAPSHOT' || CHR(10) ||
       '============================================================================' 
|| CHR(10)  "  "
       FROM DUAL
/
SELECT BATCH_NUM BATCH, COUNT(*)  "NUMBER OF RECORDS"
FROM ACCOUNTS_SNAPSHOT_VIEW
GROUP BY BATCH_NUM
ORDER BY BATCH_NUM
/