Update ORA table with date field in 8.1 V server job

[hsahay]

Hi,

I Need to update ORA table with date and some other fileds after aggregator stage. If I define Date as "Date" data type it is not working, but if I define "Date' as a Varchar it is working, but final upadte is not working after aggregrator stage. If I remove that final update(ORA stage) it is working fine. This job is working fine in 7.5 V without any problem.

Please let me know if anyone have any idea how to update

Here are the details for update

Query type is :'Generate Update action from Options and Columns tabs'
update action : Update existing rows or insert new ones

query: UPDATE STG_ACH_DLY_SEQ_NUM SET DLY_SEQ_NUM=:1,LST_RUN_DT=TO_DATE(:2, 'DD-MON-YY'),CCD_SEQ_NUM=:4 WHERE STG_ACH_DLY_SEQ_NUM_ID=:3;
INSERT INTO STG_ACH_DLY_SEQ_NUM (DLY_SEQ_NUM,LST_RUN_DT,STG_ACH_DLY_SEQ_NUM_ID,CCD_SEQ_NUM) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'),:3,:4)


Thanks,

[chulett]

You need to explain what all those "not working" statements mean. What's not working about them? Are you getting errors? Also, are you ensuring that your date is in the format that matches the mask used in the generated DML?

[hsahay]

You need to explain what all those "not working" statements mean. What's not working about them? Are you getting errors? Also, are you ensuring that your date is in the format that matches the mask used in the generated DML?

IF date is in date data type. Getting "DATE conversion failed" error . If I used Varchar , job is in running status only. For testing If I remove update stage , the job completed successfuly. The final date(in varchar) which i need to update is like this --->2010-11-28. in Date data type
it is like ---->15673


In date data type the generated UPDATE DML is like this

UPDATE STG_ACH_DLY_SEQ_NUM SET DLY_SEQ_NUM=:1,LST_RUN_DT=TO_DATE(:2, 'DD-MON-YY'),CCD_SEQ_NUM=:4 WHERE STG_ACH_DLY_SEQ_NUM_ID=:3;
INSERT INTO STG_ACH_DLY_SEQ_NUM (DLY_SEQ_NUM,LST_RUN_DT,STG_ACH_DLY_SEQ_NUM_ID,CCD_SEQ_NUM) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'),:3,:4)


If date is in Varchar type the generated Update DML is


UPDATE STG_ACH_DLY_SEQ_NUM SET DLY_SEQ_NUM=:1,LST_RUN_DT=:2,CCD_SEQ_NUM=:4 WHERE STG_ACH_DLY_SEQ_NUM_ID=:3;
INSERT INTO STG_ACH_DLY_SEQ_NUM (DLY_SEQ_NUM,LST_RUN_DT,STG_ACH_DLY_SEQ_NUM_ID,CCD_SEQ_NUM) VALUES (:1,:2,:3,:4)

For testing I removed even data field, still the update is not working.
Please advise.

Thanks

[chulett]

Keep the target as a DATE in the Oracle stage but make it a varchar in the job. How does the date come in from your source? Typically one would use IConv/OConv to convert it from whatever your source format is to the format your DML is looking for, from your example it would be looking for "DD-MON-YYYY".

If that YYYY-MM-DD format that you posted is what you get from the source, then off the top of my head something like:

Code: Select all

OConv(IConv(YourDate,"D"),"D-DMY[2,3,2]")

should work.

[hsahay]

Thanks for you reply.

I am using 'Varchar' for sourse date from oracle stage and contiuting that 'Varchar' thoughout the job. I used 'Date' in the final update in the Oracle stage. I also I am using Iconv and Oconv too.

When I used Query TPE'Generate Update action from Options and Columns tabs'
and Update action;: Update existing rows or insert new ones

Ds generates automaticaly for date filed like TO_DATE(:2, 'DD-MON-YY').

I used even to OCONV(converteddatesting "D2") to get date in that formate.But I am getting the following warning.

while processing column "LST_RUN_DT"
Value treated as NULL
Attempt to convert String value "28 NOV 10" to Date type unsuccessful.

and this syntax OConv(IConv(YourDate,"D"),"D-DMY[2,3,2]") is also not working.

advance thanks for help

[chulett]

Attempt to convert String value "28 NOV 10" to Date type unsuccessful.

That's close but you need the dashes between the segments for it to work properly.