Hi,
I am using Oracle 9i and Datastage 7.5 Sever Edition.
When I write a user defined sql for inserting into target table (Oracle) and update action is User -defined sql, and run the job, it gives warning message
is not supported for pre-defined update action.
The source is Oracle table.
Is user defined sql not allowed for target stage or there is some way of writing it?
It works fine in the input stage (select from table)
Thanks
Ketfos
Action - is not supported for pre-defined update action
Moderators: chulett, rschirm, roy
Re: Action - is not supported for pre-defined update action
There is an option in the Update action for user defined SQL.It is same as how you write user defined SQL for input stage.the thing is, it is DML statement.ketfos wrote:Hi,
I am using Oracle 9i and Datastage 7.5 Sever Edition.
When I write a user defined sql for inserting into target table (Oracle) and update action is User -defined sql, and run the job, it gives warning message
is not supported for pre-defined update action.
The source is Oracle table.
Is user defined sql not allowed for target stage or there is some way of writing it?
It works fine in the input stage (select from table)
Thanks
Ketfos
hope this helps.
RK
Hi,
Below is the user defined sql for insert into target table.
TestPol..SchemaTABLENAMEACTION: INSERT INTO POLICY(POLICY_NUMBER,POLICY_YEAR,POLICY_SUFFIX,GROUP_NUMBER,BILL_TYPE,BILL_ACCOUNT_TYPE, POSTING_DATE, PAYMENT_DATE, PAYMENT_AMOUNT, PAYROLL_FROM_DATE, PAYROLL_TO_DATE,
ALLOC_FROM_INITIAL_PREMIUM, SOURCE_TEXT, PAYMENT_TRANSFER_TYPE, CHARGE_OFF_RECOVERY_CODE,UPDATE_DATE)
VALUES (:1,:2,:3,:4,:5,:6,
TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),
TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'),
:9,
TO_DATE(:10, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),
:12,:13,:14,:15,TO_DATE(:16, 'YYYY-MM-DD HH24:MI:SS'));
is not supported for pre-defined update action.
Thsk
ketfos
Below is the user defined sql for insert into target table.
TestPol..SchemaTABLENAMEACTION: INSERT INTO POLICY(POLICY_NUMBER,POLICY_YEAR,POLICY_SUFFIX,GROUP_NUMBER,BILL_TYPE,BILL_ACCOUNT_TYPE, POSTING_DATE, PAYMENT_DATE, PAYMENT_AMOUNT, PAYROLL_FROM_DATE, PAYROLL_TO_DATE,
ALLOC_FROM_INITIAL_PREMIUM, SOURCE_TEXT, PAYMENT_TRANSFER_TYPE, CHARGE_OFF_RECOVERY_CODE,UPDATE_DATE)
VALUES (:1,:2,:3,:4,:5,:6,
TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),
TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'),
:9,
TO_DATE(:10, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),
:12,:13,:14,:15,TO_DATE(:16, 'YYYY-MM-DD HH24:MI:SS'));
is not supported for pre-defined update action.
Thsk
ketfos
Hi,
Your suggestion did help and now I not getting the previous error message
"is not supported for pre-defined update action. "
The error message is
ORA-01036: illegal variable name/number
The user defined sql in target table -Oracle
INSERT INTO TESTPAY
(POLICY_NUMBER,POLICY_YEAR,POLICY_SUFFIX ,GROUP_NUMBER,
BILL_TYPE,BILL_ACCOUNT_TYPE,POSTING_DATE,PAYMENT_DATE,PAYMENT_AMOUNT,
PAYROLL_FROM_DATE,PAYROLL_TO_DATE, ALLOC_FROM_INITIAL_PREMIUM ,SOURCE_TEXT, PMT_TRANSFER_TYPE, CHARGE_OFF_RECOVERY_CODE )
VALUES (:1,:2,:3,:4,:5,:6,TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'),
:9,TO_DATE(:10, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),:12,:13,:14,:15)
How do I tace this error?
Thks
Ketfos
Your suggestion did help and now I not getting the previous error message
"is not supported for pre-defined update action. "
The error message is
ORA-01036: illegal variable name/number
The user defined sql in target table -Oracle
INSERT INTO TESTPAY
(POLICY_NUMBER,POLICY_YEAR,POLICY_SUFFIX ,GROUP_NUMBER,
BILL_TYPE,BILL_ACCOUNT_TYPE,POSTING_DATE,PAYMENT_DATE,PAYMENT_AMOUNT,
PAYROLL_FROM_DATE,PAYROLL_TO_DATE, ALLOC_FROM_INITIAL_PREMIUM ,SOURCE_TEXT, PMT_TRANSFER_TYPE, CHARGE_OFF_RECOVERY_CODE )
VALUES (:1,:2,:3,:4,:5,:6,TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'),
:9,TO_DATE(:10, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),:12,:13,:14,:15)
How do I tace this error?
Thks
Ketfos