sqlldr sql expressions in ctl file?

From: Carmen Rusu <rusucarmen_at_gmail.com>
Date: Thu, 10 Sep 2009 13:34:30 -0500
Message-ID: <76c686d80909101134r5b2896afgcb0071bc034af245_at_mail.gmail.com>

The control file below works without the last line "DURATION". I am trying to make it also calculate the DURATION. With the DURATION line it gives me the error Oracle xxxx INVALID NUMBER.

Please note that it uses two other previously evaluated values above it :CURRENT_STATUS_DT_MONTH and :SERV_BEG_DT_MONTH

DURATION "MONTHS_BETWEEN(TO_DATE(TO_CHAR(:CURRENT_STATUS_DT_MONTH,
'YYYYMMDD'), 'YYYYMMDD'), TO_DATE(TO_CHAR(:SERV_BEG_DT_MONTH, 'YYYYMMDD'),
'YYYYMMDD'))"

Any ideas how can I make it work? Just trying to compute all three values in one pass.

LOAD DATA
APPEND INTO TABLE paymt_etl.payment_claims_care_stg FIELDS TERMINATED BY ';'
trailing nullcols
(

MEDICAID_NUM            INTEGER EXTERNAL,
SERV_GRP                CHAR,
PROV_NUM                INTEGER EXTERNAL,
ICN                     INTEGER EXTERNAL,
ICN_SEQ_NUM             INTEGER EXTERNAL,
STATUS                  CHAR,
FORCE_PAY_IND           CHAR,
SERV_BEG_DT             DATE "YYYYMMDD" ,
SERV_END_DT             DATE "YYYYMMDD" ,
BILL_CD                 CHAR,
UNIT_RATE               FLOAT EXTERNAL,
UNITS_PAID              FLOAT EXTERNAL,
PAYMENT_AMT             FLOAT EXTERNAL,
PAYMENT_AMT2            FLOAT EXTERNAL,
CLAIM_SUB_DT            DATE "YYYYMMDD" ,
CURRENT_STATUS_DT       DATE "YYYYMMDD" ,
AUTH_AGENCY_CD          CHAR,
APP_INC_AMT             FLOAT EXTERNAL,
COPAYMENT_AMT           FLOAT EXTERNAL,
SERV_CD                 CHAR,
ITEM_CD                 CHAR,
PROC_CD                 CHAR,
FUND_CD                 CHAR,
MED_TYPE_PROG           CHAR,
CAT_COVERAGE            CHAR,
ELIG_COVERAGE_CD1       CHAR,
TPR_FLAG                CHAR,
SSN                     CHAR,
CURRENT_NAME            CHAR,
BIRTH_DT                DATE "YYYYMMDD" ,
GENDER                  CHAR,
COUNTY_CD               CHAR,
DIAG_VER_NUM            INTEGER EXTERNAL,
DIAG_CD1                CHAR,
DIAG_CD2                CHAR,
DIAG_CD3                CHAR,
DIAG_CD4                CHAR,
DIAG_CD5                CHAR,
PAY_LVLSRV_TYP_CD       CHAR,
PAY_LVLSRV              CHAR,
LEAVE_DAYS              FLOAT EXTERNAL,
BILLED_AMT              FLOAT EXTERNAL,
PROV_CMS_DLN            INTEGER EXTERNAL,
PROV_PROV_HOLD_CD       CHAR,
PROV_COMPONENT_CD       CHAR,
PROV_PAYEE_ID_NUM       INTEGER EXTERNAL,
PROV_BUDGET_NUM         INTEGER EXTERNAL,

CURRENT_STATUS_DT_MONTH "TRUNC(TO_DATE(:CURRENT_STATUS_DT, 'YYYYMMDD'),
'MONTH')",

SERV_BEG_DT_MONTH "TRUNC(TO_DATE(:SERV_BEG_DT, 'YYYYMMDD'), 'MONTH')", DURATION "MONTHS_BETWEEN(TO_DATE(TO_CHAR(:CURRENT_STATUS_DT_MONTH,
'YYYYMMDD'), 'YYYYMMDD'), TO_DATE(TO_CHAR(:SERV_BEG_DT_MONTH, 'YYYYMMDD'),
'YYYYMMDD'))"

)

Thank you,

Carmen Rusu
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 10 2009 - 13:34:30 CDT