One Star

newbie: automatic schema creation for query?

Hi, forgive me if this question has been answered, but I've gone through the intro tutorials and the manual and still am missing something.
I have a select query pulling various volumns from multiple tables. I simply want to export the result set, including column headers, to an Excel file.
I've saved the query in the Repository and dragged it onto the workspace. I have also created a tFileOutputExcel object on the workspace. I can join the two files together and run the process. However, the wrong fields are dumped into the Excel file. When I look at the query details, it looks like it is using an entirely unrelated table-based schema.
Do I need to manually create a schema that contains the columns which I've specified in the query? If so, how do I create a schema that is not based on any one table? If not, how can I tell TOS to export the columns specified in the select query?
Thanks in advance for your help!
3 REPLIES
Community Manager

Re: newbie: automatic schema creation for query?

Hello
Do I need to manually create a schema that contains the columns which I've specified in the query? If so, how do I create a schema that is not based on any one table? If not, how can I tell TOS to export the columns specified in the select query?

You need to manually create a schema that contains the columns which you have specified in the query, for example:
"SELECT person.id, person.name
FROM person"
you should create a shema include two columns: id and name. When you define the schema, the Db column should be same as the fileds in db table.
I have a select query pulling various volumns from multiple tables. I simply want to export the result set, including column headers, to an Excel file.

You can use serval tDBxxxinput components to select columns from different tables, then link to a tMap components and do some join or merge the data.
Feel free to post your questions!
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: newbie: automatic schema creation for query?

I have a select query pulling various volumns from multiple tables. I simply want to export the result set, including column headers, to an Excel file.

You can use serval tDBxxxinput components to select columns from different tables, then link to a tMap components and do some join or merge the data.

Do we have to use one tDBxxxinput component for each table we want to query in a database?
My situation :
I have an input database and I want to make a query on several linked tables in it.
I defined a Db connection with all the tables schemas in my repository.
I add a tAccessInput component in my job.
The basics settings for the tAccessInput component wants me to choose one TABLE schema only.
I can't figure out what I have to do to be able to specify several table names and schemas for my query.
Thanks for your answer and your help!
Helene.
One Star

Re: newbie: automatic schema creation for query?

Follow the previous post :
When I try to build my query with the SQLBuilder, I can use the designer to define the tables, the fields and the relations I want in my query.
The SQL statement producted is syntaxically correct, but, when I run it, no result appears in the "Query results view"(I just have the SQL statement).
Despite this problem, If I want to use this query in the basics settings of my tAccessInput component (by filling the "Query Type" fields), how can I fill right the other fields ("Table Name", "Table Schema"....) to allow the query to run on all the tables referenced ?
Thanks again for the help.
Helene.