8.1 is behaving differently than 7.0
Posted: Thu May 13, 2010 8:23 am
Hi All,
we are currenntly having problems with a job that we have migrated from 7.0 (SE) to 8.1 (EE)
Update Query type
"update action from options and column tabs"
update action
"Insert rows without clearing"
generated sql version 7:
INSERT INTO PRODUCT (PRODUCT_SEQ_NO,TIME_STAMP,PRODUCT_ID,PRODUCT_TYPE,ISSUE_GROUP_ID,
PRODUCT_GROUP_ID,ALCO_GROUP_ID,PRODUCT_DESCRIPTION,
LAST_UPDATE_DATE,REPAYMENT_TYPE,VERSO_PRODUCT_YN,
MONITOR_GROUP_CODE,DAILY_INTEREST_YN,FLEXIBLE_YN,
CASHBACK_YN,BONUS_YEAR,BONUS_PERCENTAGE,MARKET_CATEGORY,PROCURATION_FEE,PROCURATION_FEE_PERCENTAGE,
TRANCHE_AMOUNT,ADDITIONAL_TRANCHE_AMOUNT,
LATEST_INTEREST_RATE,PRODUCT_CATEGORY,FLOOR_RATE,
CAPPED_RATE,TRANSFER_PRICE,INDEX_LINKED_YN,NPV,ROC,
AFFINITY,FLOOR_UNTIL_DATE,FLOOR_PERIOD,CAPPED_UNTIL_DATE,
CAPPED_PERIOD,BONUS_AMOUNT,BONUS_PAY_DATE,
BONUS_MONTHS_PERIOD,CASHBACK_AMOUNT,
CASHBACK_PERCENT,PG_DESC,REDEM_PENALTY_INCEPTION,
REDEM_PENALTY_AMOUNT,REDEM_PENALTY_PERIOD,
REDEM_PENALTY_END_DATE,FIX_DATE,FIX_PERIOD)
VALUES
(:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),:3,:4,:5,:6,:7,:8,TO_DATE(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,:11,:12,:13,:14,:15,:16,:17,
:18,:19,:20,:21,:22,:23,
:24,:25,:26,:27,:28,:29,:30,:31,TO_DATE(:32, 'YYYY-MM-DD HH24:MI:SS'),:33,
TO_DATE(:34, 'YYYY-MM-DD HH24:MI:SS'),:35,:36,
TO_DATE(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,
TO_DATE(:45, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(:46, 'YYYY-MM-DD HH24:MI:SS'),:47)
generated sql version 8:
INSERT INTO PRODUCT (PRODUCT_SEQ_NO,TIME_STAMP,
PRODUCT_ID,PRODUCT_TYPE,ISSUE_GROUP_ID,PRODUCT_GROUP_ID,
ALCO_GROUP_ID,PRODUCT_DESCRIPTION,LAST_UPDATE_DATE,
REPAYMENT_TYPE,VERSO_PRODUCT_YN,MONITOR_GROUP_CODE,
DAILY_INTEREST_YN,FLEXIBLE_YN,CASHBACK_YN,BONUS_YEAR,
BONUS_PERCENTAGE,MARKET_CATEGORY,PROCURATION_FEE,
PROCURATION_FEE_PERCENTAGE,TRANCHE_AMOUNT,
ADDITIONAL_TRANCHE_AMOUNT,LATEST_INTEREST_RATE,
PRODUCT_CATEGORY,FLOOR_RATE,CAPPED_RATE,
TRANSFER_PRICE,INDEX_LINKED_YN,NPV,ROC,AFFINITY,
FLOOR_UNTIL_DATE,FLOOR_PERIOD,CAPPED_UNTIL_DATE,
CAPPED_PERIOD,BONUS_AMOUNT,BONUS_PAY_DATE,
BONUS_MONTHS_PERIOD,CASHBACK_AMOUNT,CASHBACK_PERCENT,
PG_DESC,REDEM_PENALTY_INCEPTION,REDEM_PENALTY_AMOUNT,
REDEM_PENALTY_PERIOD,REDEM_PENALTY_END_DATE,FIX_DATE,
FIX_PERIOD)
VALUES
(:1,TO_TIMESTAMP(:2, 'YYYY-MM-DD HH24:MI:SS'),:3,:4,:5,:6,:7,:8,TO_TIMESTAMP(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,
TO_TIMESTAMP(:32, 'YYYY-MM-DD HH24:MI:SS'),
:33,TO_TIMESTAMP(:34, 'YYYY-MM-DD HH24:MI:SS'),:35,:36,TO_TIMESTAMP(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,
TO_TIMESTAMP(:45, 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP(:46, 'YYYY-MM-DD HH24:MI:SS'),:47)
We are using Oracle 10g and the structure of the table is as follows:
Describing product....
NAME Null? Type
------------------------------- --------- -----
PRODUCT_SEQ_NO NOT NULL NUMBER(10,0)
TIME_STAMP NOT NULL DATE
cont.....
Its as if the DS engine is interperating the destination type as a TIMESTAMP rather than a DATE as specified in th table.
One thing to mention is that when this was originally coded in ver 7 ORAOCI8 was used and before import i "cleaned" the dsx by replacing all the ORAOCI8 to ORAOCI9
Has anyone else come up against this and/or know how to "fix" it?
we are currenntly having problems with a job that we have migrated from 7.0 (SE) to 8.1 (EE)
Update Query type
"update action from options and column tabs"
update action
"Insert rows without clearing"
generated sql version 7:
INSERT INTO PRODUCT (PRODUCT_SEQ_NO,TIME_STAMP,PRODUCT_ID,PRODUCT_TYPE,ISSUE_GROUP_ID,
PRODUCT_GROUP_ID,ALCO_GROUP_ID,PRODUCT_DESCRIPTION,
LAST_UPDATE_DATE,REPAYMENT_TYPE,VERSO_PRODUCT_YN,
MONITOR_GROUP_CODE,DAILY_INTEREST_YN,FLEXIBLE_YN,
CASHBACK_YN,BONUS_YEAR,BONUS_PERCENTAGE,MARKET_CATEGORY,PROCURATION_FEE,PROCURATION_FEE_PERCENTAGE,
TRANCHE_AMOUNT,ADDITIONAL_TRANCHE_AMOUNT,
LATEST_INTEREST_RATE,PRODUCT_CATEGORY,FLOOR_RATE,
CAPPED_RATE,TRANSFER_PRICE,INDEX_LINKED_YN,NPV,ROC,
AFFINITY,FLOOR_UNTIL_DATE,FLOOR_PERIOD,CAPPED_UNTIL_DATE,
CAPPED_PERIOD,BONUS_AMOUNT,BONUS_PAY_DATE,
BONUS_MONTHS_PERIOD,CASHBACK_AMOUNT,
CASHBACK_PERCENT,PG_DESC,REDEM_PENALTY_INCEPTION,
REDEM_PENALTY_AMOUNT,REDEM_PENALTY_PERIOD,
REDEM_PENALTY_END_DATE,FIX_DATE,FIX_PERIOD)
VALUES
(:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),:3,:4,:5,:6,:7,:8,TO_DATE(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,:11,:12,:13,:14,:15,:16,:17,
:18,:19,:20,:21,:22,:23,
:24,:25,:26,:27,:28,:29,:30,:31,TO_DATE(:32, 'YYYY-MM-DD HH24:MI:SS'),:33,
TO_DATE(:34, 'YYYY-MM-DD HH24:MI:SS'),:35,:36,
TO_DATE(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,
TO_DATE(:45, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(:46, 'YYYY-MM-DD HH24:MI:SS'),:47)
generated sql version 8:
INSERT INTO PRODUCT (PRODUCT_SEQ_NO,TIME_STAMP,
PRODUCT_ID,PRODUCT_TYPE,ISSUE_GROUP_ID,PRODUCT_GROUP_ID,
ALCO_GROUP_ID,PRODUCT_DESCRIPTION,LAST_UPDATE_DATE,
REPAYMENT_TYPE,VERSO_PRODUCT_YN,MONITOR_GROUP_CODE,
DAILY_INTEREST_YN,FLEXIBLE_YN,CASHBACK_YN,BONUS_YEAR,
BONUS_PERCENTAGE,MARKET_CATEGORY,PROCURATION_FEE,
PROCURATION_FEE_PERCENTAGE,TRANCHE_AMOUNT,
ADDITIONAL_TRANCHE_AMOUNT,LATEST_INTEREST_RATE,
PRODUCT_CATEGORY,FLOOR_RATE,CAPPED_RATE,
TRANSFER_PRICE,INDEX_LINKED_YN,NPV,ROC,AFFINITY,
FLOOR_UNTIL_DATE,FLOOR_PERIOD,CAPPED_UNTIL_DATE,
CAPPED_PERIOD,BONUS_AMOUNT,BONUS_PAY_DATE,
BONUS_MONTHS_PERIOD,CASHBACK_AMOUNT,CASHBACK_PERCENT,
PG_DESC,REDEM_PENALTY_INCEPTION,REDEM_PENALTY_AMOUNT,
REDEM_PENALTY_PERIOD,REDEM_PENALTY_END_DATE,FIX_DATE,
FIX_PERIOD)
VALUES
(:1,TO_TIMESTAMP(:2, 'YYYY-MM-DD HH24:MI:SS'),:3,:4,:5,:6,:7,:8,TO_TIMESTAMP(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,
TO_TIMESTAMP(:32, 'YYYY-MM-DD HH24:MI:SS'),
:33,TO_TIMESTAMP(:34, 'YYYY-MM-DD HH24:MI:SS'),:35,:36,TO_TIMESTAMP(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,
TO_TIMESTAMP(:45, 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP(:46, 'YYYY-MM-DD HH24:MI:SS'),:47)
We are using Oracle 10g and the structure of the table is as follows:
Describing product....
NAME Null? Type
------------------------------- --------- -----
PRODUCT_SEQ_NO NOT NULL NUMBER(10,0)
TIME_STAMP NOT NULL DATE
cont.....
Its as if the DS engine is interperating the destination type as a TIMESTAMP rather than a DATE as specified in th table.
One thing to mention is that when this was originally coded in ver 7 ORAOCI8 was used and before import i "cleaned" the dsx by replacing all the ORAOCI8 to ORAOCI9
Has anyone else come up against this and/or know how to "fix" it?