Action - is not supported for pre-defined update action

[ketfos]

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

[g_rkrish]

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

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.

hope this helps.

[chulett]

It must be something with how you wrote your sql. Post it.

[ketfos]

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

[chulett]

A couple of suggestions: remove the outer parens wrapping the entire sql and remove the trailing semi-colon. Neither are needed and may be confusing things.

[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

[chulett]

Mismatch between the numered parameter markers in the sql and the columns defined in the stage.