tDBOutput - Update

[jimbo20814]

I had Talend Support looking into as they can't figure it out either, for now at least. So I wanted to as Community as well.

I have needs where I need to update Fact records, so they do not have primary key field. I'm using "use field options" in the Advanced tab, specifying what the update key is, in this case it's three fields: a string, integer and date fields.

I know for 100% certainty, I quadruple checked that what's coming out of tMap_2 has joining records (again on those three fields) in Fact_TimeSlotMeeting_Temp but Fact_TimeSlotMeeting_Temp is not updating. Also when I try "update or insert", it does not find the joining records and always tries to do an insert.

An anyone shed light here on why it's not working?

[tnewbie]

Jimbo,

Two reasons i could think of:

1) You might not have checked the "Updatable" check box...in this again make sure that the update key is not updatable

2) The values you are trying to update to are resulting in Ref Integrity constraint violations ...just thinking loud

[jimbo20814]

Please see below, the fields are update-able. And the three keys are not set to update-able.

[tnewbie]

Ok...1 last thing I would ask you to check is, please see if there are any extra characters, spaces , upper case, lower case letters in the incoming values that are failing the compare operation. 

[jimbo20814]

I filtered the input from Stg_Lenses to a single MeetingID for troubleshooting purposes. I put a tLogRow to show the data coming out of tMap_2. The second screenshot is the SQL data from Fact_TimeSlotMeeting_Temp. You can see Dim_UserID/ActivityDate/MeetingID match, even on case. But it's not updating, the MeetingSubject on Fact_TimeSlotMeeting_Temp should change to "Fix The Glitch" but it's not.

[tnewbie]

Ok...one last thing i would try is, instead of taking all 3 values to determine the match, take ID first if it is able to match then take ID and Date and if it is also matching take all three and run the test, i am suspecting meeting ID has some extra spaces at the end (just a guess)...if not i don't know what else can be the problem

[jimbo20814]

Thanks tnewbie!

The culprit is ActivityDate. I tried your suggestion, one by one. So with Dim_UserID and MeetingID, it finds the join! So it performs the update. When I add ActivityDate, it doesn't find the match. The destination SQL datatype is date. In Talend I tried "dd-MM-yyyy", "MM-dd-yyyy" and "yyyy-MM-dd" but none of them finds the match. Any suggestions?

[tnewbie]

I guessed so...what is the data type of the activitydate column in source and targets?

[jimbo20814]

Below is the ActivityDate tMap source. The ActivityDate SQL destination datatype is date.

[jimbo20814 · Accepted solution]

All, I found the issue. ActivityDate is created from a datetime in a tJavaRow. In the tJavaRow, I still had the hours/min/seconds to ActivityDate. I didn't know that if you make the schema "yyyy-MM-dd" it will still hold onto the hours/min/seconds. I went back to the tJavaRow and made sure to remove the hours/min/seconds. It was then able to match on ActivityDate on the destination table.