Re: sqlldr sql expressions in ctl file?
- From: Carmen Rusu <rusucarmen@xxxxxxxxx>
- To: Jackie Brock <J.Brock@xxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 11 Sep 2009 09:43:00 -0500
If I take to_char out I get "invalid month".
Thank you,
Carmen Rusu
Oracle DBA
On Thu, Sep 10, 2009 at 3:15 PM, Jackie Brock <J.Brock@xxxxxxxxxxxxx> wrote:
> It looks like you could/should take out the TO_CHARs you have on that
> line??
>
> -Jackie
>
> Jackie D. Brock
> Database Specialist - Systems Evaluation
> CableLabs®
> 858 Coal Creek Circle
> Louisville, CO 80027
> Email: j.brock@xxxxxxxxxxxxx
> 303-661-3347
>
>
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Carmen Rusu
> *Sent:* Thursday, September 10, 2009 12:35 PM
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* sqlldr sql expressions in ctl file?
>
> 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
>
>
Other related posts:
