Hi Rachel,
Just tried it and it works. Thanks for your suggestion.
Lisa
-----Original Message-----
Sent: Tuesday, December 31, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L
dumb question -- did you extract the insert statement and run it in
sqlplus? Does it run there or does it go boom as well?
- "Koivu, Lisa" <Lisa.Koivu_at_efairfield.com> wrote:
> You asked for it. Here goes. It is partitioned on julian_run_date.
> Please
> don't hammer me about the design. I wasn't given a chance to improve
> on it.
> It is basically a mainframe file that I have stored history of.
>
> SQL> desc vegas_mart
> Name Null? Type
> ----------------------------- -------- ------------------
> ID NOT NULL NUMBER(38)
> CONTRACT_NUMBER NOT NULL NUMBER(9)
> LOAD_JOB_ID NOT NULL NUMBER(12)
> JULIAN_RUN_DATE NOT NULL NUMBER(12)
> FAC_CODE VARCHAR2(1)
> OWNER_LAST_NAME VARCHAR2(20)
> OWNER_FIRST_NAME VARCHAR2(15)
> OWNER_ADDRESS1 VARCHAR2(25)
> OWNER_ADDRESS2 VARCHAR2(25)
> OWNER_CITY VARCHAR2(18)
> OWNER_STATE VARCHAR2(2)
> OWNER_ZIP VARCHAR2(9)
> NET_PURCHASE_PRICE NUMBER(11,2)
> ORIGINAL_DOWN_PAYMENT NUMBER(11,2)
> TOTAL_DOWN_PAYMENT NUMBER(11,2)
> CR_BAL NUMBER(11,2)
> INTEREST_RATE NUMBER(5,2)
> FIRST_PAYMENT_DATE DATE
> QUALIFICATION_CODE VARCHAR2(1)
> PAYMENT_AMOUNT NUMBER(9,2)
> PAYMENT_FREQUENCY VARCHAR2(1)
> AGING_10_TO_30_DAYS_DUE NUMBER(9,2)
> AGING_31_TO_60_DAYS_DUE NUMBER(9,2)
> AGING_61_TO_90_DAYS_DUE NUMBER(9,2)
> ASSIGNED_LOAN_ADMIN_REP VARCHAR2(2)
> DATE_OF_SALE DATE
> STATUS_OF_ACCOUNT VARCHAR2(1)
> CONTRACT_TYPE VARCHAR2(1)
> WAS_PENDER VARCHAR2(1)
> CREDIT_LIFE_ON_CONTRACT VARCHAR2(1)
> DOCUMENT_STATUS_CODE VARCHAR2(1)
> FIXED_WEEK_SALE VARCHAR2(1)
> UDI_SALE VARCHAR2(1)
> PHASE_NUMBER VARCHAR2(6)
> FAIRSHARE_PLUS_MEMBER VARCHAR2(1)
> POINTS_OWNED NUMBER(7)
> DEED_DATE DATE
> PRE_AUTH_DRAFT_ACCOUNT VARCHAR2(1)
> RESERVATION_CODE VARCHAR2(4)
> INTERNATIONAL_CODE VARCHAR2(1)
> UNIT_PHASE_COMPLETION_CODE VARCHAR2(1)
> AGING_0_TO_90_DAYS_DUE NUMBER(9,2)
> AGING_91_TO_120_DAYS_DUE NUMBER(9,2)
> AGING_121_TO_150_DAYS_DUE NUMBER(9,2)
> AGING_151_OVER NUMBER(9,2)
> LOT_LOCATION VARCHAR2(12)
> PAYMENTS_MADE NUMBER(3)
> SUPPRESSION_CODE VARCHAR2(1)
> ACCRUED_INTEREST_BAL NUMBER(9,2)
> PAC_FREEZE_CODE VARCHAR2(1)
> CREDIT_CARD_FREEZE_CODE VARCHAR2(1)
> ASSOCIATION_NUMBER VARCHAR2(4)
> RFS_ASSIGNMENT_DATE DATE
> OVERRIDE_MAINT_FEE_BALANCE VARCHAR2(1)
> RESERVATION_PENDING VARCHAR2(1)
> CREDIT_REPORTING_CODE VARCHAR2(2)
> CANCEL_DEFERMENT_REASON_CODE VARCHAR2(2)
> EQUITY_IN NUMBER(9,2)
> DATE_CODED_7 DATE
> ADJUSTABLE_RATE_MORTGAGE VARCHAR2(1)
> NUMBER_OF_PAYS_LEFT NUMBER(4)
> DEFERRED_INTEREST NUMBER(9,2)
> DEFERRED_PRINCIPAL NUMBER(9,2)
> CURRENT_YEAR_DEFERMENTS NUMBER(5)
> CURRENT_YEAR_TOTAL_DEFERMENTS NUMBER(5)
> LAST_PAYMENT_DATE DATE
> NEXT_PAYMENT_DATE DATE
> PAC_DUE_DATE DATE
> EFT_ROUTING_NUMBER VARCHAR2(8)
> EFT_ACCOUNT_NUMBER VARCHAR2(30)
> EFT_MANUAL_NUMBER VARCHAR2(30)
> BANK VARCHAR2(35)
> STATUS_CHANGE_DATE DATE
> ASSIGNED_LOAN_REP VARCHAR2(2)
> CREDIT_CARD_PAC_ACCOUNT VARCHAR2(20)
> PRINCIPAL_BALANCE NUMBER(11,2)
> CR_DISCOUNT_BALANCE NUMBER(11,2)
> CREDIT_LIFE_PREM_BAL NUMBER(11,2)
> RFP_PAC_CODE VARCHAR2(1)
> RFP_DRAFT_CODE VARCHAR2(1)
> RFP_ROUTE_NUMBER VARCHAR2(8)
> RFP_EFT_NUMBER VARCHAR2(30)
> RFP_MANUAL_NUMBER VARCHAR2(30)
> RFP_BANK_NAME VARCHAR2(35)
> FPPA_PAC_CODE VARCHAR2(1)
> FPPA_DRAFT_CODE VARCHAR2(1)
> FPPA_ROUTE_NUMBER VARCHAR2(8)
> FPPA_EFT_NUMBER VARCHAR2(30)
> FPPA_MANUAL_NUMBER VARCHAR2(30)
> FPPA_BANK_NAME VARCHAR2(35)
> TS_BAL_DUE_RECOGNIZED NUMBER(9)
> TS_LATE_FEE_RECOGNIZED NUMBER(9,2)
> TS_YTD_MAINT_FEE_COLL NUMBER(9,2)
> TS_MAINT_FEE_AMOUNT NUMBER(9,2)
> PO_BIRTH_DATE DATE
> TS_LOCATION VARCHAR2(12)
> CR_DATE_REC_IN_DEEDING DATE
> CR_REFUND VARCHAR2(11)
> CR_CREDIT_LIFE_TYPE VARCHAR2(1)
> CR_QUALIFICATION_DATE DATE
> CR_EQT_IN_FROM_CONT_NO1 VARCHAR2(9)
> CR_EQT_IN_FROM_CONT_NO2 VARCHAR2(9)
> CR_HC_AMT1 NUMBER(9)
> CR_HC_AMT2 NUMBER(9)
> CR_HC_POST1 NUMBER(9)
> CR_HC_POST2 NUMBER(9)
> CR_HC_DATE1 DATE
> CR_HC_DATE2 DATE
> CR_DATE_IN_LR DATE
> CR_TRADE_ALLOW NUMBER(9)
> CR_TITLE_INS_CHARGED NUMBER(11)
> CR_TITLE_INS_COLLECTED NUMBER(11)
> CR_FILING_FEE_CHARGED NUMBER(11)
> CR_FILING_FEE_COLLECTED NUMBER(11)
> CR_ACCRUED_INT_BAL_RSV NUMBER(11)
> CR_LATE_FEE_BAL_RSV NUMBER(11)
> AREA_CODE VARCHAR2(3)
> PHONE_NUMBER VARCHAR2(7)
> PAID_OFF VARCHAR2(1)
> EDIT_DATE_TIME DATE
> BIANNUAL_FLAG VARCHAR2(10)
> FICO_SCORE NUMBER(3)
> SOCIAL_SECURITY_NUMBER NUMBER(9)
>
> SQL>
>
> Here's the code. Line 1970 is cr_hc_amt2.
>
> -- Insert the record into the reject table.
> INSERT INTO vegas_mart_reject
> SELECT
> id,
> load_date,
> load_job_id_v,
> contract_number,
> fac_code,
> owner_last_name,
> owner_first_name,
> owner_address1,
> owner_address2,
> owner_city,
> owner_state,
> owner_zip,
> net_purchase_price,
> original_down_payment,
> total_down_payment,
> cr_bal,
> interest_rate,
> first_payment_date,
> qualification_code,
> payment_amount,
> payment_frequency,
> aging_10_to_30_days_due,
> aging_31_to_60_days_due,
> aging_61_to_90_days_due,
> assigned_loan_admin_rep,
> date_of_sale,
> status_of_account,
> contract_type,
> was_pender,
> credit_life_on_contract,
> document_status_code,
> fixed_week_sale,
> udi_sale,
> phase_number,
> fairshare_plus_member,
> points_owned,
> deed_date,
> pre_auth_draft_account,
> reservation_code,
> international_code,
> unit_phase_completion_code,
> aging_0_to_90_days_due,
> aging_91_to_120_days_due,
> aging_121_to_150_days_due,
> aging_151_over,
> lot_location,
> payments_made,
> suppression_code,
> accrued_interest_bal,
> pac_freeze_code,
> credit_card_freeze_code,
> association_number,
> rfs_assignment_date,
> override_maint_fee_balance,
> reservation_pending,
> credit_reporting_code,
> cancel_deferment_reason_code,
> equity_in,
> date_coded_7,
> adjustable_rate_mortgage,
> number_of_pays_left,
>
=== message truncated ===
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Koivu, Lisa
INET: Lisa.Koivu_at_efairfield.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 31 2002 - 14:03:52 CST