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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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