topic Re: SCD Type2 without component issue in Talend Studio

SCD Type2 without component issue

Anonymous — Sat, 16 Nov 2024 03:53:27 GMT

Hi All,
I'm creating SCD type 2 after getting data from SRC table and LKP table to tmap i'm creating a variable to show me what is the new record and what is the update record

new record condition : IF(ISNULL(SURRID)),True,False
update record condition : IF(NOT ISNULL(SURRID) AND SRC.EmailAddress Not Equal LKP.EmailAddress),True,False
as shown below

Re: SCD Type2 without component issue

tnewbie — Mon, 09 Dec 2019 17:02:28 GMT

Hi Fawad,

Though i didnt find any documentation on Talend, which confirms this, my experience taught me use ".equal" vs "=" when you try to compare string values. See using row2.SURRID.equals(null) makes any difference.

Re: SCD Type2 without component issue

Anonymous — Mon, 09 Dec 2019 17:22:11 GMT

Hi Tnewbie,

I already tried it but it gives the error message
cannot invoke equals string on the primitive type int error.

Thanks

Re: SCD Type2 without component issue

tnewbie — Mon, 09 Dec 2019 17:35:40 GMT

Hi Fawaad,

From your job design, looks like you are joining on CustomerID where SURRID seems to be a key column in your lookup table, so at any point of time you will never get your SURRID as null if you have a match, did you try checking the existence of CustomerID vs SURRID, additionally CustomerID is an int column so your query performance will also be elevated with int comparison vs string comparison.

Also, Is there a reason why you have to manually do the SCD2 determination, instead of using Talend built components? I am extensively using SCD components tDBSCD (SCD Type 2) and i don't have any issues so far. Did you try using SCD components?

Re: SCD Type2 without component issue

Anonymous — Mon, 09 Dec 2019 21:11:05 GMT

Hi Tnewbie,
Please imagine that we are inserting a new record from the source then when the lookup done the record will not be exist in the lookup table and here the SURRID = null.
I didn't use the component because as the when a record updated I need the end date of the old record to be the start date of the new record minus 1 day not the

Re: SCD Type2 without component issue

tnewbie — Mon, 09 Dec 2019 22:12:14 GMT

Fawaad,

I am not sure if i am understanding the problem correctly...anyway, 1 last things i would try suggesting from my end before i give up 🙂 You can probably try the tmap setting, where you can capture non matching records...pls check the attachment

tmap filter expressions.png