topic Re: Join by SQL Statement in Talend Studio

Join by SQL Statement

Anonymous — Sat, 16 Nov 2024 12:40:51 GMT

Hi,
I want to join 2 tables and filter the data according to some rules.
I already have the sql statement for this operation from my colleague.
So, is there anyway I can directly use this sql statement to join and filter the data from these 2 tables?
or do I have to use tMap to put in the conditions?
Please reply ASAP..
Thank You.
Kyaw Lwin Phyo

Re: Join by SQL Statement

Anonymous — Mon, 19 Sep 2011 04:13:32 GMT

Hi
You can use txxxRow such as tMysqlRow the execute the sql statement if the sql statement have already achieved the request join two table and filter data. Or use a tMap to do the join and add filter condition to filter the data.
Best regards
Shong

Re: Join by SQL Statement

Anonymous — Mon, 19 Sep 2011 04:15:17 GMT

Hi Shong,
Thanks for the fast response...
What kinda txxxRow should I use?
My input is from oracle and output is to MySQL.
Thanks again...
Kyaw Lwin Phyo

Re: Join by SQL Statement

Anonymous — Mon, 19 Sep 2011 04:24:54 GMT

Hi
Please use tMap component and create the general job if the input and output tables are in different database type.
Best regards
Shong

Re: Join by SQL Statement

alevy — Mon, 19 Sep 2011 12:30:26 GMT

You can put any select statement you want in your input component (join, group by, where, order by, nested) as long as the schema you define is consistent with the result of the query.

Re: Join by SQL Statement

Anonymous — Tue, 20 Sep 2011 09:14:12 GMT

Hello...
I have put a query in tMySqlInput component and direct this component to tMap.
but in tMap, the result from my query is not there but the standard columns from the table.
My table has Year, Week, Month, Start Date, End Date columns
and my query returns the concated value of YearWeek-Start DateEnd Date as period.
However, in tMap, I'm only seeing the columns but not my returned value.
I've tried with tMySqlRow too.
I need this period variable to validate the data from another table which I need to join.
Any Suggestion????
Thank you.
Kyaw Lwin Phyo

Re: Join by SQL Statement

Anonymous — Tue, 20 Sep 2011 09:31:45 GMT

Hi
Please upload some screenshots of job or show us an example to explain your request, so that we could help you further.
Best regards
Shong

Re: Join by SQL Statement

alevy — Tue, 20 Sep 2011 09:39:15 GMT

Put up a screenshot of your job and the tMySqlInput settings, particularly the query and schema

Re: Join by SQL Statement

janhess — Tue, 20 Sep 2011 09:55:06 GMT

Did you do a guess schema for your query?

Re: Join by SQL Statement

Anonymous — Tue, 20 Sep 2011 10:07:56 GMT

I have uploaded the screenshots
First one is how I'm trying to do.
I have one table that has data and another weeks table which has the dates
I have to filter the data from first table with the dates from weeks table.
Second one is my query.
In fact the full query is like
getting the period value below to check the local_date of the table.
"SELECT concat(w.year, w.week, date_format(w.`Start Date`, '%d-%b-%Y'), date_format(w.`End Date`, '%d-%b-%Y')) period
FROM calendar.weeks w
WHERE date_sub(curdate(), interval 7 day) >= w.`Start Date`
AND date_sub(curdate(), interval 7 day) <= w.`End Date`; => period = 20113604-Sep-201110-Sep-2011"
Third one is how I'm getting in tMap.
Thank You.....
(if images are not available please also tell me how to upload images..
I've tried from the link https://community.talend.com/t5/Design-and-Development/how-to-upload-an-image/td-p/60550)

Re: Join by SQL Statement

alevy — Wed, 21 Sep 2011 13:32:11 GMT

As you say, your SQL is returning a single column only (the concated value of YearWeek-Start DateEnd Date) but the schema for the component has the standard columns from the table. Change the schema to match the results of the query. That is why there is a warning icon against the component.

Re: Join by SQL Statement

Anonymous — Thu, 09 Feb 2012 10:30:00 GMT

Hi,
I want to join 2 tables and filter the data according to some rules.
I already have the sql statement for this operation from my colleague.
So, is there anyway I can directly use this sql statement to join and filter the data from these 2 tables?
or do I have to use tMap to put in the conditions?
Please reply ASAP..
Thank You.
Kyaw Lwin Phyo

SQL application is also prone to corruption like an other application. It may get corrupted or damaged due to various reasons. SQL become damaged due to virus attck, power failure or due to improper shut down. When SQL database become corrupted it becomes impossible for SQL user to make join statement. It needs My SQL repair

Re: Join by SQL Statement

janhess — Thu, 09 Feb 2012 11:01:04 GMT

You need to edit the schema from your query to reflect what is being returned. It looks as though you just have a default table schema defined.