topic Re: Join 2 Different Tables of Different Database in Talend Studio

Join 2 Different Tables of Different Database

abhi90 — Fri, 02 Mar 2018 13:11:03 GMT

I have to join 2 Different Tables of 2 Different Database. One from SQL Server and other is from Oracle. Now In Informatica we used 1 Dummy Column each for 2 and joined them. After Both database Input Components I kept tReplace to Replace a String each. Now when I am joining them in tmap creating 1 Dummy Column each for both Input Sets it is saying Dummy Columns can't be resolved or is not a variable. I have to filter out data after tmap in below way

(TO_DATE(Date Column of SQL SERVER,'YYYY-MM-DD') >= DATE COLUMN OF ORACLE and TO_DATE(Date Column of SQL SERVER,'YYYY-MM-DD') <= DATE COLUMN OF ORACLE) AND UPPER(Brand column of SQL server)=UPPER(BRAND Column of Oracle Table). If anyone can help will be really helpful to him

Re: Join 2 Different Tables of Different Database

abhi90 — Fri, 02 Mar 2018 13:21:13 GMT

Hi Richard,Shong

Can you please advise

Re: Join 2 Different Tables of Different Database

Anonymous — Fri, 02 Mar 2018 13:37:18 GMT

I'm not sure why you are using "Dummy Columns", is there not a natural key field which will help you join? Maybe if you show me your job design it will help me understand the issue. If you can do it in Informatica, you can do it in Talend...but not necessarily in the same way.

Re: Join 2 Different Tables of Different Database

abhi90 — Fri, 02 Mar 2018 14:16:17 GMT

actually There is no such column to join between Them.So creating Dummy Columns.

tmssqlinput(select columns from Table)--->treplace(replacing a string)--->

toracleinput(select columns from Table)--->treplace(replacing a string)--->tmap(joining on those 2 Dummy Columns)----Have to filter out based on my condition----

Re: Join 2 Different Tables of Different Database

abhi90 — Sat, 03 Mar 2018 20:47:52 GMT

Can anybody please help for the filter condition which i have mentioned.

Re: Join 2 Different Tables of Different Database

abhi90 — Sun, 04 Mar 2018 13:31:26 GMT

Hi Richard,

Now joining is happening. But unable to do the Filtering.

After tmap I have to do a filter in below way(which we did in Informatica)

(TO_DATE(Date Column of SQL SERVER,'YYYY-MM-DD') >= (DATE COLUMN OF ORACLE which is in MM/DD/YYYY Format) and TO_DATE(Date Column of SQL SERVER,'YYYY-MM-DD') <= (DATE COLUMN OF ORACLE which is in MM/DD/YYYY Format)) AND UPPER(Brand column of SQL server)=UPPER(BRAND Column of Oracle Table).

Re: Join 2 Different Tables of Different Database

manodwhb — Mon, 05 Mar 2018 05:40:24 GMT

in a tMap outpout section Activate.unactivate expression filter to do the filter.

Re: Join 2 Different Tables of Different Database

abhi90 — Mon, 05 Mar 2018 06:24:44 GMT

Hi Manohar,

There I am doing it. But I am trying with TalendDate.DiffDate where Filtering not happening. So asking what talendDate function I have to use?

Re: Join 2 Different Tables of Different Database

manodwhb — Mon, 05 Mar 2018 06:42:32 GMT

use TalendDate.compareDate(myDate,myDate2) in tmap

Re: Join 2 Different Tables of Different Database

manodwhb — Mon, 05 Mar 2018 12:16:24 GMT

@abhi90 Did it help you ?

Re: Join 2 Different Tables of Different Database

abhi90 — Tue, 06 Mar 2018 16:18:25 GMT

manodwhb

Can you help me in filtering?actually have very less knowledge with this function.

TalendDate.compareDate(myDate,myDate2,"MM/dd/yyyy")>0 is it something like be?

Now MyDate2 is coming from SQL SERVER. which is in yyyy-MM-dd and myDate is in MM/DD/YYYY format. so what will be the filtering condition which we used in Informatica?

(TO_DATE(Date,'YYYY-MM-DD') >= FROM_DATE and TO_DATE(Date,'YYYY-MM-DD') <= TO_DATE)

Re: Join 2 Different Tables of Different Database

manodwhb — Wed, 07 Mar 2018 04:35:46 GMT

you can use TalendDate.compareDate(myDate,myDate2) funtion to filter.

Re: Join 2 Different Tables of Different Database

manodwhb — Wed, 07 Mar 2018 11:45:07 GMT

since i have compared using below way.

TalendDate.compareDate(utils.stringToDate(row1.PeriodStart,"MM/dd/yyyy"),row2.Valid_From) > 0

Re: Join 2 Different Tables of Different Database

manodwhb — Tue, 20 Mar 2018 06:53:39 GMT

did it help you?