So I played with this for a couple of days before getting it to work for me... When using the QUERY clause in a EXPDP/IMPDP that includes dates you should not wrap them in a TO_DATE. I tried using a condition like below

QUERY=CLM_HDR:"WHERE HDR_PD_DT <= TO_DATE('04302001','MMDDYYYY')"

EXPDP would give me an error stating that a right parenthesis was missing, but it was obviously not. I did finally figure out that it wanted the dates in the default date format. So by changing it to this

QUERY=CLM_HDR:"WHERE HDR_PD_DT <= '04/30/2001'"

It worked.

Here is my entire parameter file used for the export job I was attempting.

DIRECTORY=datapump_dir

DUMPFILE=102000-042001_CLM_ARCHIVE_%U.dmp

LOGFILE=102000-042001_CLM_ARCHIVE.log

CONTENT=ALL

FILESIZE=2G

INCLUDE=TABLE:"IN ('CLM_HDR','CLM_HDR_ADDL_EOB','CLM_HDR_ADJ_VD','CLM_HDR_COND_CD',

'CLM_HDR_DIAG','CLM_HDR_HCFA1500_DENTAL','CLM_HDR_HH_CERT',

'CLM_HDR_ICD','CLM_HDR_OCC_CD','CLM_HDR_OCC_SPN','CLM_HDR_PREV_LOCN','CLM_HDR_PYR',

'CLM_HDR_RA','CLM_HDR_RLTD_HIST','CLM_HDR_UB92','CLM_HDR_VALU_CD','CLM_HDR_XOVR',

'CLM_LI','CLM_LI_AMB','CLM_LI_ATTACH','CLM_LI_BSE_CHNG','CLM_LI_EXC','CLM_LI_TPL_BLNG')"

QUERY=CLM_HDR:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_ADDL_EOB:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_ADJ_VD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_COND_CD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_DIAG:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_HCFA1500_DENTAL:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_HH_CERT:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_ICD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_OCC_CD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_OCC_SPN:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_PREV_LOCN:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_PYR:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_RA:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_RLTD_HIST:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_UB92:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_VALU_CD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_HDR_XOVR:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_LI:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_LI_AMB:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_LI_ATTACH:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_LI_BSE_CHNG:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_LI_EXC:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",

CLM_LI_TPL_BLNG:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')"

## Friday, May 9, 2008

Subscribe to:
Post Comments (Atom)

## No comments:

Post a Comment