Join 2 Different Tables of Different Database

Eight Stars

Join 2 Different Tables of Different Database

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

 

Eight Stars

Re: Join 2 Different Tables of Different Database

Hi Richard,Shong

Can you please advise

Sixteen Stars

Re: Join 2 Different Tables of Different Database

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.

Eight Stars

Re: Join 2 Different Tables of Different Database

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----

Eight Stars

Re: Join 2 Different Tables of Different Database

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

Re: Join 2 Different Tables of Different Database

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).

Thirteen Stars

Re: Join 2 Different Tables of Different Database

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

Manohar B
Eight Stars

Re: Join 2 Different Tables of Different Database

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? 

 

Thirteen Stars

Re: Join 2 Different Tables of Different Database

use TalendDate.compareDate(myDate,myDate2) in tmap
Manohar B
Thirteen Stars

Re: Join 2 Different Tables of Different Database

@abhi90 Did it help you ?

Manohar B
Eight Stars

Re: Join 2 Different Tables of Different Database

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)

Thirteen Stars

Re: Join 2 Different Tables of Different Database

you can use TalendDate.compareDate(myDate,myDate2) funtion to filter.
Manohar B
Thirteen Stars

Re: Join 2 Different Tables of Different Database

since i have compared using below way.

 

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

Manohar B
Highlighted
Thirteen Stars

Re: Join 2 Different Tables of Different Database

did it help you?

Manohar B