topic Re: Transform input data using SQL query and pass results to output in Talend Studio

Transform input data using SQL query and pass results to output

Anonymous — Fri, 15 Feb 2019 11:48:41 GMT

Hello,

I am trying to transfer my data from data source to my data warehouse. I do not want all the columns from the input data, so have written an SQL query that transforms the input data. I want this transformed data to be transferred to the output.

Any suggestions on how this can be achieved?

Thanks!

Re: Transform input data using SQL query and pass results to output

manodwhb — Fri, 15 Feb 2019 11:58:05 GMT

@Harguna ,lets suppose your data base is oracle,you write a query in tDBinput by specifying the required column in the select clause and the same fields you need to keep in the schema of tDBinput .

Re: Transform input data using SQL query and pass results to output

manodwhb — Fri, 15 Feb 2019 11:59:43 GMT

@Harguna ,check the below screenshot will help you.

Re: Transform input data using SQL query and pass results to output

Anonymous — Fri, 15 Feb 2019 12:02:20 GMT

I have some columns which actually don't exist in the table but are defined in the query. How do I map these columns in tMap?

Re: Transform input data using SQL query and pass results to output

manodwhb — Fri, 15 Feb 2019 12:09:50 GMT

@Harguna ,check the below link will be useful how to use alias names in the query and those alias names should be there in the schema of tDBInput.

https://help.talend.com/reader/iYcvdknuprDzYycT3WRU8w/pdP5qPPaAORn8RIiHmlY0A

Re: Transform input data using SQL query and pass results to output

Anonymous — Fri, 15 Feb 2019 12:18:46 GMT

1. Do I always need to add "id" in the SQL query (otherwise it gives bad value error)?

2. Do I have to add alias columns in the context part of the input table to solve my mapping problem?

Re: Transform input data using SQL query and pass results to output

akumar2301 — Fri, 15 Feb 2019 12:22:52 GMT

if column is not part of table , you could use query like ,

select id, name , 'Valid record' status from employee

"select id, name ," + CONTEXT.FIXEDSTRING + " status from employee"

you need to defined id,name and status as your schema.

Re: Transform input data using SQL query and pass results to output

Anonymous — Fri, 15 Feb 2019 12:24:47 GMT

But how do I map these alias columns in the tMap?

Re: Transform input data using SQL query and pass results to output

akumar2301 — Fri, 15 Feb 2019 12:28:22 GMT

if these are defined as schema of txxxInput component andd attach it to tMap , all the columns, including alias, will be part of tMap

Re: Transform input data using SQL query and pass results to output

manodwhb — Fri, 15 Feb 2019 12:36:08 GMT

@Harguna ,check the below screenshots.