Transform input data using SQL query and pass results to output

[Anonymous]

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!

[manodwhb]

@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 .

[manodwhb]

@Harguna ,check the below screenshot will help you.

[Anonymous]

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?

[manodwhb]

@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

[Anonymous]

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?

[akumar2301]

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

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

or

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

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

[Anonymous]

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

[akumar2301]

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

[manodwhb · Accepted solution]

@Harguna ,check the below screenshots.