Siebel Data Quality Administration Guide > Sample Configuration and Script Files > Sample SQL Scripts >

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

/