Re: sqlldr sql expressions in ctl file?

From: Carmen Rusu <rusucarmen_at_gmail.com>
Date: Fri, 11 Sep 2009 10:39:17 -0500
Message-ID: <76c686d80909110839m10917c3dr29a277dfb69c38f3_at_mail.gmail.com>

Jackie and Tom, thank you for your help. Here's what works:

-------rest of ctl file removed
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(TRUNC(TO_DATE(:CURRENT_STATUS_DT, 'YYYYMMDD'), 'MONTH'),TRUNC(TO_DATE(:SERV_BEG_DT, 'YYYYMMDD'), 'MONTH'))" )

vs what did not work with or without to_char:

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'))"
)
I used Jackie's idea and basically copied the sql statements defining current_status_month and serv_beg_dt_month again into the months_between statement.

I was hoping to avoid extra work by re-using the values just computed above and put in current_status_dt_month and serv_beg_dt_month as bind variables. Looks like Oracle didnt like this and wants to re-compute them again from current_status_dt and serv_beg_dt data fields.

Thank you again,

Carmen
On Fri, Sep 11, 2009 at 10:18 AM, Mercadante, Thomas F (LABOR) < Thomas.Mercadante_at_labor.state.ny.us> wrote:

> Carmen,
>
>
> The Months_between function acts on a date data type. You are passing it a
> character data type. So it is trying to auto-convert the char value into a
> date and failing.
>
>
>
>
>
> I think you need something like:
>
>
>
> DURATION "MONTHS_BETWEEN(TO_DATE(:CURRENT_STATUS_DT_MONTH, 'YYYYMMDD'),
> TO_DATE(:SERV_BEG_DT_MONTH, 'YYYYMMDD'))"
>
>
>
> That is if your Current_status_dt_month and Serv_beg_dt_month are coming in
> as the format above.
>
>
>
> Try experimenting with the months_between function. It is really basic and
> easy to use.
>
>
> Hope this helps.
>
>
>
> Tom
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Carmen Rusu
> *Sent:* Friday, September 11, 2009 10:43 AM
> *To:* Jackie Brock; oracle-l_at_freelists.org
> *Subject:* Re: sqlldr sql expressions in ctl file?
>
>
>
> 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_at_cablelabs.com>
> 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_at_cablelabs.com
>
> 303-661-3347
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Carmen Rusu
> *Sent:* Thursday, September 10, 2009 12:35 PM
> *To:* oracle-l_at_freelists.org
> *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
>
>
>

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

Received on Fri Sep 11 2009 - 10:39:17 CDT