Talend community forum / [resolved] Update override

vivekjayapalan · 2011-06-02 11:23:43

Tags: [database, date, error, error management, java, oracle, schema]

Hello,

I have requirement in Talend, to do an Update to the target target, It is same like Update Override in Informatica.

For doing update override in Talend i am using tOracleRow on the Target table, and below is the update statement which i am using inside tOracleRow

"UPDATE Table1
SET Table1.DWH_VALIDFLAG = 0,
Table1.VALIDTO = to_date('"+row2.VALIDTO+"','YYYY-MM-DD HH12:MI:SS')
WHERE Table1.ID = "+row2.ID+"
AND Table1.DWH_VALIDFLAG = 1
AND Table1.DWH_INSERTED_AT <> to_date('"+row2.DWH_INSERTED_AT+"','YYYY-MM-DD HH12:MI:SS')"

Here in the above statement i am trying to Update 2 columns DWH_VALIDFLAG which is Number data type and VALIDTO column which is of Date Data type, While Updating VALIDTO i am getting an Oracle Error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0"

I have 22 records as update, all has same value for VALIDTO column that is '2011-05-23 09:12:33' so there is no question of invalid year, and i have also followed "yyyy-MM-dd HH:mm:ss" Date Pattern for this field through out the mapping.

I am wondering why i am getting this error? has anybody have any idea on the above issue.

Thanks,
Vivek Jayapalan

shong · 2011-06-02 11:29:03

Hi
- Can you upload a screenshot of job? I think row2.VALIDTO is null or 0 now and it is not passed to tOracleRow component.
- Change the expression to:
to_date('"+row2.VALIDTO+"','yyyy-mm-dd hh:mi:ss')

Best regards
Shong

vivekjayapalan · 2011-06-02 12:36:34

Hello Shong,

you are correct, i just checked now i am getting DWH_INSERTED column as null, not VALIDTO column as null, i am passing 3 columns as Input, row2.ID, row2.VALIDTO and row2.DWH_INSERTED_AT.

Before reaching tOracleRow i am doing aggregation using tAggregateRow Group by ID and getting Min of another column to get VALIDTO value, But i dont want to perform any aggregation for DWH_INSERTED_AT column, i want to just pass through it, so i am not doing any action for DWH_INSERTED_AT, but i am getting null, is there any option perform aggregation at column level.

Thanks,
Vivek Jayapalan

Last edited by vivekjayapalan (2011-06-02 12:37:03)

vivekjayapalan · 2011-06-02 13:42:58

Hello Shong,

I am attaching the screen shot of the aggregator transformation of Informatica,

Here in Informatica i am grouping by ID and taking MIN(VALIDTO_In) and DWH_INSERTED_AT is passed like that, In Informatica DWH_INSERTED_AT will be taken automatically from the record which is having MINIMUM VALIDTO_IN value and pass forward.

Is there an solution to do the same in Talend.

Thanks,
Vivek Jayapalan

Last edited by vivekjayapalan (2011-06-03 12:07:39)

vivekjayapalan · 2011-06-03 12:04:34

Hello Shong,

As you said there was null value generated in one of the date column using for update,

But now i managed to get actual value for the columns, but i still getting the same error : ORA-01841: (full) year must be between -4713 and +9999, and not be 0.

I also changed the expression to to_date('"+row2.VALIDTO+"','yyyy-mm-dd hh:mi:ss'),

I have attached the screen shot of the error message and the value of the date columns, i get only valid dates and there is no 0 or null value generated.

Can you help me on this.

Regards,
Vivek Jayapalan

Uploaded Images

Last edited by vivekjayapalan (2011-06-03 12:14:26)

shong · 2011-06-03 12:11:27

Hi
I am working on your topic, but I don't see your screenshots, can you export the job and send it to me?

Best regards
Shong

vivekjayapalan · 2011-06-06 07:13:32

Hello Shong,

I am waiting for you reply on the above issue.

I am not sure whether there is a mistake from my end or Is it a Talend Bug, Please let me know ASAP.

Should i raise it as a bug?

Regards,
Vivek Jayapalan

vivekjayapalan · 2011-06-07 13:52:43

Hello Shong,

I have found solution for this issue, Basically we can't pass Column with DATE Datatype to a Customized UPDATE statement.

So we need to convert all the columns which are used in Update statement with DATE datatype to STRING Datatype like Talend.formatDate("yyyy-MM-dd HH:mm:ss",row1.VALIDTO) in a tMap before reaching the tOracleRow and convert those column again in the UPDATE Statement to DATE Datatype as i have used in one of the Update statement above.

And this works absolutely fine.

Thanks a lot for your support.

Regards,
Vivek Jayapalan

Last edited by vivekjayapalan (2011-06-07 13:59:10)

Announcement

Unanswered posts

#1 2011-06-02 11:23:43

[resolved] Update override

#2 2011-06-02 11:29:03

Re: [resolved] Update override

#3 2011-06-02 12:36:34

Re: [resolved] Update override

#4 2011-06-02 13:42:58

Re: [resolved] Update override

#5 2011-06-03 12:04:34

Re: [resolved] Update override

#6 2011-06-03 12:11:27

Re: [resolved] Update override

#7 2011-06-06 07:13:32

Re: [resolved] Update override

#8 2011-06-07 13:52:43

Re: [resolved] Update override

Board footer