topic Re: [resolved] retrieve results of a custom sql query from a database connection in Talend Studio

[resolved] retrieve results of a custom sql query from a database connection

Anonymous — Sat, 16 Nov 2024 13:05:13 GMT

Hi,
I want to be able to retrieve the result set of a complex sql query from a mysql database. The query joins many tables and includes custom where clauses to limit the resultset.
I used multiple tMysqlinput components as input into a tMap and this then outputs to a single tMysqlOutput. The tMap does the mappings and joins (not able to add additional where clauses).
However when I run this job, I notice that for each input component, ALL of the data is retrieved from the database before pipe-lining to the tMap.
I actually only want to get back the resultset of the query, is this possibly?
This is using TMDMCE-Studio-All-r50363-V4.1.1
Thanks in advance

Re: [resolved] retrieve results of a custom sql query from a database connection

Anonymous — Thu, 17 Feb 2011 14:54:47 GMT

You can write your query with subjoins in tMysqlinput. The schema defined shoud contain all the fieds in the select query.
eg:
select id,name,number from empdetails dtl,empdata data
where.....
id, name and number shoud be defined in the schema.

Re: [resolved] retrieve results of a custom sql query from a database connection

Anonymous — Thu, 17 Feb 2011 15:29:18 GMT

Thanks for the reply lijolawrance.
So I deleted the table name from the component view and set the Query Type to Built In and then edited the sql and used the query that I had saved under the DB Connections/name/Queries. However I cannot see where I can create a custom schema to reflect the result set. I thought it may be in the View Schemas, but I cannot find where to create that.
Any help would be appreciated.
Thanks

Re: [resolved] retrieve results of a custom sql query from a database connection

Anonymous — Thu, 17 Feb 2011 17:58:11 GMT

Its the Edit schema button in tMysqlinput where you need to define the resultset.

Re: [resolved] retrieve results of a custom sql query from a database connection

Anonymous — Fri, 18 Feb 2011 07:58:35 GMT

Thanks, I could not do it until I realised that I needed to select the 'Built In' Schema type