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

IDS_IDT_CONTACT_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 CONTACTS_SNAPSHOT_VIEW;

CREATE MATERIALIZED VIEW CONTACTS_SNAPSHOT_VIEW AS

SELECT

   T1.CONTACT_ID CONTACT_ID,

   T2.FST_NAME || ' ' || LAST_NAME NAME,

   T2.MID_NAME MIDDLE_NAME,

   T3.ROW_ID ADDRESS_ID,

   T3.CITY CITY,

   T3.COUNTRY COUNTRY,

   T3.ZIPCODE POSTAL_CODE,

   T3.STATE STATE,

   T3.ADDR STREETADDRESS,

   T3.ADDR_LINE_2 ADDRESS_LINE2,

   DECODE(T2.PR_PER_ADDR_ID,T3.ROW_ID,'Y','N') PRIMARY_FLAG,

   T4.NAME ACCOUNT,

   T2.BIRTH_DT BirthDate,

   T2.CELL_PH_NUM CellularPhone,

   T2.EMAIL_ADDR EmailAddress,

   T2.HOME_PH_NUM HomePhone,

   T2.SOC_SECURITY_NUM SocialSecurityNumber,

   T2.WORK_PH_NUM WorkPhone,

   FLOOR((ROWNUM-1)/&BATCHSIZE)+1 BATCH_NUM

FROM

   dbo.S_CON_ADDR T1,

   dbo.S_CONTACT T2,

   dbo.S_ADDR_PER T3,

   dbo.S_ORG_EXT T4

WHERE

   T1.CONTACT_ID = T2.ROW_ID AND

   T1.ADDR_PER_ID = T3.ROW_ID AND

-- OR (T1.ADDR_PER_ID IS NULL)) Do we need contacts with no address?

--Comment the following line for Multiple address match option

-- T2.PR_PER_ADDR_ID = T3.ROW_ID (+) AND

T2.PR_DEPT_OU_ID = T4.PAR_ROW_ID (+)

/

SELECT '============================================================================' || CHR(10) ||

' REPORT ON CONTACTS SNAPSHOT' || CHR(10) ||

'============================================================================' || CHR(10) " "

FROM DUAL

/

SELECT BATCH_NUM BATCH, COUNT(*) "NUMBER OF RECORDS"

FROM CONTACTS_SNAPSHOT_VIEW

GROUP BY BATCH_NUM

ORDER BY BATCH_NUM

/