INSERT/SELECT ERROR

UDA
Enthusiast

INSERT/SELECT ERROR

I am trying to perform an INSERT/SELECT statement, but I keep getting a "invalid operation on an ANSI datetime or interval value" error. However, I am not doing any date functions in the statement. Could someone please review and let me know what the problem may be?

Thank you in advance.

Statement:

INSERT INTO DCCU_DW.F_LOAN
SELECT RANK() OVER (ORDER BY NQ.ACCOUNT_ID, NQ.ID_SRC) LOAN_ID
, NQ.*
FROM (
SELECT COALESCE(A.ACCOUNT_ID, -1) ACCOUNT_ID
, COALESCE(LNT.LOAN_TYPE_ID, -1) LOAN_TYPE_ID
, COALESCE(LC.LOAN_CODE_ID, -1) LOAN_CODE_ID
, L.REFERENCE REFERENCE
, COALESCE(B.BRANCH_ID, -1) BRANCH_ID
, COALESCE(LP.LOAN_PURPOSE_ID, -1) LOAN_PURPOSE_ID
, L.VIN VIN
, L.FIRSTPAYMENTDATE FIRST_PAYMENT_DATE
, L.LASTPAYMENTDATE LAST_PAYMENT_DATE
, COALESCE(PT.PAYMENT_TYPE_ID, -1) PAYMENT_TYPE_ID
, L.PAYMENTMETHOD PAYMENT_METHOD
, L.PAYMENTFREQUENCY PAYMENT_FREQUENCY
, L.ORIGINALDATE ORIGINAL_DATE
, L.OPENDATE OPEN_DATE
, L.CLOSEDATE CLOSE_DATE
, L.DUEDAY1 DUE_DAY_1
, L.DUEDAY2 DUE_DAY_2
, L.DUEDATE DUE_DATE
, 0 LOAN_TO_VALUE
, COALESCE(PCT.PAYMENT_CALC_TYPE_ID, -1) PAYMENT_CALC_TYPE_ID
, L.PAYMENTSKIPS PAYMENT_SKIPS
, L.PAYMENTSKIPSTARTDAY PAYMENT_SKIP_START_DAY
, L.PAYMENTCOUNT PAYMENT_COUNT
, 0 PAYMENTS_MADE
, 0 DQ_DAYS_21_30
, 0 DQ_DAYS_31_60
, 0 DQ_DAYS_61_90
, 0 DQ_DAYS_91_120
, 0 DQ_DAYS_120_UP
, COALESCE(LCT.LATE_CHARGE_TYPE_ID, -1) LATE_CHARGE_TYPE_ID
, L.DQNOTICENUMBER DQ_NOTICE_NUMBER
, L.DQNOTICEDATE DQ_NOTICE_DATE
, L.CHARGEOFFDATE CHARGE_OFF_DATE
, COALESCE(COT.CHARGE_OFF_TYPE_ID, -1) CHARGE_OFF_TYPE_ID
, L.CHARGEOFFAMOUNT CHARGE_OFF_AMOUNT
, L.BALANCE LOAN_BALANCE
, L.ORIGINALBALANCE ORIGINAL_BALANCE
, L.CREDITLIMIT CREDIT_LIMIT
, 0 AVAILABLE_CREDIT
, L.AVAILCREDITCALC AVAIL_CREDIT_CALC
, L.NOTENUMBER NOTE_NUMBER
, L.ADVANCEAMOUNT ADVANCE_AMOUNT
, L.LASTADVANCEDATE LAST_ADVANCE_DATE
, L.PAYMENT PAYMENT_AMOUNT
, L.COLLATERALCODE COLLATERAL_CODE_ID
, COALESCE(AC.USER_ID, -1) APPROVAL_CODE
, L.APPROVALDATE APPROVAL_DATE
, COALESCE(INTR.INTEREST_TYPE_ID, -1) INTEREST_TYPE_ID
, L.INTERESTRATE INTEREST_RATE
, L.INTERESTDATE INTEREST_DATE
, L.INTERESTUNPAID INTEREST_UNPAID
, L.INTERESTYTD INTEREST_YTD
, L.INTERESTRATEINDEX INTEREST_RATE_INDEX
, L.INTERESTRATEMARGIN INTEREST_RATE_MARGIN
, L.INTERESTRATEMARGINSIGN INTEREST_RATE_MARGIN_SIGN
, L.INTERESTRATEMIN INTEREST_RATE_MIN
, L.INTERESTRATEMAX INTEREST_RATE_MAX
, L.PRINCIPALYTD PRINCIPAL_YTD
, L.PRINCIPALLASTYEAR PRINCIPAL_LAST_YEAR
, L.REGECOUNT REG_E_COUNT
, COALESCE(INSUR.INSURANCE_TYPE_ID, -1) INSURANCE_TYPE_ID
, L.INSURANCEMAX INSURANCE_MAX
, L.INSURANCEBALANCE INSURANCE_BALANCE
, L.SINGLEPREMIUMLIFE SINGLE_PREMIUM_LIFE
, L.SINGLEPREMIUMDISABILITY SINGLE_PREMIUM_DISABILITY
, L.FIRSTINSBIRTHDATE FIRST_INS_BIRTH_DATE
, L.SECONDINSBIRTHDATE SECOND_INS_BIRTH_DATE
, L.BALLOONDATE BALLOON_DATE
, L.BALLOONAMOUNT BALLOON_AMOUNT
, L.MATURITYDATE MATURITY_DATE
, L.STATEMENTGROUP STATEMENT_GROUP
, L.STATEMENTMAILCODE STATEMENT_MAIL_CODE
, COALESCE(EC.ECOA_CODE_ID, -1) ECOA_CODE_ID
, L.CREDITREPORTNUMBER CREDIT_REPORT_NUMBER
, L.CREDITSCORE LOAN_CREDIT_SCORE
, L.WARNINGCODE1 WARNING_CODE_1
, L.WARNINGCODE2 WARNING_CODE_2
, L.WARNINGCODE3 WARNING_CODE_3
, L.WARNINGCODE4 WARNING_CODE_4
, L.WARNINGCODE5 WARNING_CODE_5
, L.WARNINGCODE6 WARNING_CODE_6
, L.WARNINGCODE7 WARNING_CODE_7
, L.WARNINGCODE8 WARNING_CODE_8
, L.SERVICE1 SERVICE_1
, L.SERVICE2 SERVICE_2
, L.SERVICE3 SERVICE_3
, L.SERVICE4 SERVICE_4
, L.SERVICE5 SERVICE_5
, L.SERVICE6 SERVICE_6
, L.SERVICE7 SERVICE_7
, L.SERVICE8 SERVICE_8
, COALESCE(AP.APPLICATION_ID, -1) APPLICATION_ID
, L.DEALERCODE DEALER_CODE
, L.PARENTACCOUNT PARENTACCOUNT_SRC
, L.APPLICATIONID APPLICATIONID_SRC
, L.APPROVALCODE APPROVALCODE_SRC
, L.BRANCH BRANCH_SRC
, L.CHARGEOFFTYPE CHARGEOFFTYPE_SRC
, L.COLLATERALCODE COLLATERALCODE_SRC
, L.ECOACODE ECOACODE_SRC
, L.INSURANCETYPE INSURANCETYPE_SRC
, L.INTERESTTYPE INTERESTTYPE_SRC
, L.LATECHARGETYPE LATECHARGETYPE_SRC
, L.LOANCODE LOANCODE_SRC
, L.PURPOSECODE PURPOSECODE_SRC
, L.ID ID_SRC
, L.TYPE_ID TYPE_SRC
, L.PAYMENTCALCTYPE PAYMENTCALCTYPE_SRC
, L.PAYMENTTYPE PAYMENTTYPE_SRC
FROM DCCU_DW.EXT_LOAN L
LEFT OUTER JOIN
DCCU_DW.L_ACCOUNT A
ON L.PARENTACCOUNT = A.ACCOUNTNUMBER_SRC
LEFT OUTER JOIN
DCCU_DW.F_APPLICATION AP
ON L.PARENTACCOUNT = AP.PARENTACCOUNT_SRC
AND L.APPLICATIONID = AP.APPLICATION_SRC
LEFT OUTER JOIN
DCCU_DW.L_USERS AC
ON L.APPROVALCODE = AC.USER_ID
LEFT OUTER JOIN
DCCU_DW.L_BRANCH B
ON L.BRANCH = B.BRANCH_SRC
LEFT OUTER JOIN
DCCU_DW.L_CHARGE_OFF_TYPE COT
ON L.CHARGEOFFTYPE = COT.CHARGEOFFTYPE_SRC
LEFT OUTER JOIN
DCCU_DW.L_COLLATERAL_CODE CC
ON L.COLLATERALCODE = CC.COLLATERALCODE_SRC
LEFT OUTER JOIN
DCCU_DW.L_ECOA_CODE EC
ON L.ECOACODE = EC.ECOACODE_SRC
LEFT OUTER JOIN
DCCU_DW.L_INSURANCE_TYPE INSUR
ON L.INSURANCETYPE = INSUR.INSURANCETYPE_SRC
LEFT OUTER JOIN
DCCU_DW.L_INTEREST_TYPE INTR
ON L.INTERESTTYPE = INTR.INTERESTTYPE_SRC
LEFT OUTER JOIN
DCCU_DW.L_LATE_CHARGE_TYPE LCT
ON L.LATECHARGETYPE = LCT.LATECHARGETYPE_SRC
LEFT OUTER JOIN
DCCU_DW.L_LOAN_CODE LC
ON L.LOANCODE = LC.LOANCODE_SRC
LEFT OUTER JOIN
DCCU_DW.L_LOAN_PURPOSE LP
ON L.PURPOSECODE = LP.PURPOSECODE_SRC
LEFT OUTER JOIN
DCCU_DW.L_LOAN_TYPE LNT
ON L.TYPE_ID = LNT.TYPE_SRC
LEFT OUTER JOIN
DCCU_DW.L_PAYMENT_CALC_TYPE PCT
ON L.PAYMENTCALCTYPE = PCT.PAYMENTCALCTYPE_SRC
LEFT OUTER JOIN
DCCU_DW.L_PAYMENT_TYPE PT
ON L.PAYMENTTYPE = PT.TYPE_SRC
) NQ;

Table Definition:

CREATE SET TABLE DCCU_DW.F_LOAN ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
LOAN_ID int NOT NULL DEFAULT -1,
APPLICATION_ID int NOT NULL DEFAULT 0,
ACCOUNT_ID int NOT NULL DEFAULT -1,
LOAN_TYPE_ID int NOT NULL DEFAULT -1,
LOAN_CODE_ID int NOT NULL DEFAULT -1,
REFERENCE char(20) NOT NULL DEFAULT ' ',
BRANCH_ID int NOT NULL DEFAULT -1,
LOAN_PURPOSE_ID int NOT NULL DEFAULT -1,
VIN char(20) NOT NULL DEFAULT ' ',
FIRST_PAYMENT_DATE TIMESTAMP NULL,
LAST_PAYMENT_DATE TIMESTAMP NULL,
PAYMENT_TYPE_ID int NOT NULL DEFAULT 0,
PAYMENT_METHOD smallint NOT NULL DEFAULT 0,
PAYMENT_FREQUENCY smallint NOT NULL DEFAULT 0,
ORIGINAL_DATE TIMESTAMP NULL,
OPEN_DATE TIMESTAMP NULL,
CLOSE_DATE TIMESTAMP NULL,
DUE_DAY_1 smallint NOT NULL DEFAULT 0,
DUE_DAY_2 smallint NOT NULL DEFAULT 0,
DUE_DATE TIMESTAMP NULL,
LOAN_TO_VALUE float NOT NULL DEFAULT 0,
PAYMENT_CALC_TYPE_ID int NOT NULL DEFAULT -1,
PAYMENT_SKIPS smallint NOT NULL DEFAULT 0,
PAYMENT_SKIP_START_DAY smallint NOT NULL DEFAULT 0,
PAYMENT_COUNT smallint NOT NULL DEFAULT 0,
PAYMENTS_MADE smallint NOT NULL DEFAULT 0,
DQ_DAYS_21_30 smallint NOT NULL DEFAULT 0,
DQ_DAYS_31_60 smallint NOT NULL DEFAULT 0,
DQ_DAYS_61_90 smallint NOT NULL DEFAULT 0,
DQ_DAYS_91_120 smallint NOT NULL DEFAULT 0,
DQ_DAYS_120_UP smallint NOT NULL DEFAULT 0,
LATE_CHARGE_TYPE_ID int NOT NULL DEFAULT -1,
DQ_NOTICE_NUMBER smallint NOT NULL DEFAULT 0,
DQ_NOTICE_DATE TIMESTAMP NULL,
CHARGE_OFF_DATE TIMESTAMP NULL,
CHARGE_OFF_TYPE_ID int NOT NULL DEFAULT -1,
CHARGE_OFF_AMOUNT numeric(19, 4) NOT NULL DEFAULT 0,
LOAN_BALANCE numeric(19, 4) NOT NULL DEFAULT 0,
ORIGINAL_BALANCE numeric(19, 4) NOT NULL DEFAULT 0,
CREDIT_LIMIT numeric(19, 4) NOT NULL DEFAULT 0,
AVAILABLE_CREDIT numeric(19, 4) NOT NULL DEFAULT 0,
AVAIL_CREDIT_CALC smallint NOT NULL DEFAULT 0,
NOTE_NUMBER char(10) NOT NULL DEFAULT ' ',
ADVANCE_AMOUNT numeric(19, 4) NOT NULL DEFAULT 0,
LAST_ADVANCE_DATE TIMESTAMP NULL,
PAYMENT_AMOUNT numeric(19, 4) NOT NULL DEFAULT 0,
COLLATERAL_CODE_ID int NOT NULL DEFAULT -1,
APPROVAL_CODE_ID int NOT NULL DEFAULT 0,
APPROVAL_DATE TIMESTAMP NULL,
INTEREST_TYPE_ID int NOT NULL DEFAULT -1,
INTEREST_RATE int NOT NULL DEFAULT 0,
INTEREST_DATE TIMESTAMP NULL,
INTEREST_UNPAID numeric(19, 4) NOT NULL DEFAULT 0,
INTEREST_YTD numeric(19, 4) NOT NULL DEFAULT 0,
INTEREST_RATE_INDEX smallint NOT NULL DEFAULT 0,
INTEREST_RATE_MARGIN int NOT NULL DEFAULT 0,
INTEREST_RATE_MARGIN_SIGN smallint NOT NULL DEFAULT 0,
INTEREST_RATE_MIN int NOT NULL DEFAULT 0,
INTEREST_RATE_MAX int NOT NULL DEFAULT 0,
PRINCIPAL_YTD numeric(19, 4) NOT NULL DEFAULT 0,
PRINCIPAL_LAST_YEAR numeric(19, 4) NOT NULL DEFAULT 0,
REGE_COUNT smallint NOT NULL DEFAULT 0,
INSURANCE_TYPE_ID int NOT NULL DEFAULT -1,
INSURANCE_MAX numeric(19, 4) NOT NULL DEFAULT 0,
INSURANCE_BALANCE numeric(19, 4) NOT NULL DEFAULT 0,
SINGLE_PREMIUM_LIFE numeric(19, 4) NOT NULL DEFAULT 0,
SINGLE_PREMIUM_DISABILITY numeric(19, 4) NOT NULL DEFAULT 0,
FIRST_INS_BIRTH_DATE TIMESTAMP NULL,
SECOND_INS_BIRTH_DATE TIMESTAMP NULL,
BALLOON_DATE TIMESTAMP NULL,
BALLOON_AMOUNT numeric(19, 4) NOT NULL DEFAULT 0,
MATURITY_DATE TIMESTAMP NULL,
STATEMENT_GROUP smallint NOT NULL DEFAULT 0,
STATEMENT_MAIL_CODE smallint NOT NULL DEFAULT 0,
ECOA_CODE_ID int NOT NULL DEFAULT -1,
CREDIT_REPORT_NUMBER char(17) NOT NULL DEFAULT ' ',
LOAN_CREDIT_SCORE int NOT NULL DEFAULT -1,
WARNING_CODE_1 smallint NOT NULL DEFAULT 0,
WARNING_CODE_2 smallint NOT NULL DEFAULT 0,
WARNING_CODE_3 smallint NOT NULL DEFAULT 0,
WARNING_CODE_4 smallint NOT NULL DEFAULT 0,
WARNING_CODE_5 smallint NOT NULL DEFAULT 0,
WARNING_CODE_6 smallint NOT NULL DEFAULT 0,
WARNING_CODE_7 smallint NOT NULL DEFAULT 0,
WARNING_CODE_8 smallint NOT NULL DEFAULT 0,
SERVICE_1 smallint NOT NULL DEFAULT 0,
SERVICE_2 smallint NOT NULL DEFAULT 0,
SERVICE_3 smallint NOT NULL DEFAULT 0,
SERVICE_4 smallint NOT NULL DEFAULT 0,
SERVICE_5 smallint NOT NULL DEFAULT 0,
SERVICE_6 smallint NOT NULL DEFAULT 0,
SERVICE_7 smallint NOT NULL DEFAULT 0,
SERVICE_8 smallint NOT NULL DEFAULT 0,
DEALER_CODE char(10) NOT NULL DEFAULT ' ',
PARENTACCOUNT_SRC char(10) NOT NULL DEFAULT ' ',
APPLICATIONID_SRC char(4) NOT NULL DEFAULT ' ',
APPROVALCODE_SRC smallint NOT NULL DEFAULT 0,
BRANCH_SRC smallint NOT NULL DEFAULT 0,
CHARGEOFFTYPE_SRC smallint NOT NULL DEFAULT 0,
COLLATERALCODE_SRC smallint NOT NULL DEFAULT 0,
ECOACODE_SRC smallint NOT NULL DEFAULT 0,
INSURANCETYPE_SRC smallint NOT NULL DEFAULT 0,
INTERESTTYPE_SRC smallint NOT NULL DEFAULT 0,
LATECHARGETYPE_SRC smallint NOT NULL DEFAULT 0,
LOANCODE_SRC smallint NOT NULL DEFAULT 0,
PURPOSECODE_SRC smallint NOT NULL DEFAULT 0,
ID_SRC char(4) NOT NULL DEFAULT ' ',
TYPE_SRC smallint NOT NULL DEFAULT 0,
PAYMENTCALCTYPE_SRC smallint NOT NULL DEFAULT 0,
PAYMENTTYPE_SRC smallint NOT NULL DEFAULT 0
)
UNIQUE PRIMARY INDEX IX_ACCOUNT_ID_LOAN_ID ( ACCOUNT_ID, LOAN_ID );
2 REPLIES
Enthusiast

Re: INSERT/SELECT ERROR

Forget that one. I had a column out of order from the select part to the insert...
Enthusiast

Re: INSERT/SELECT ERROR

can u please provide me the ddl(create table statement) for DCCU_DW.EXT_LOAN;