Transform input data using SQL query and pass results to output

Highlighted
Six Stars

Transform input data using SQL query and pass results to output

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!


Accepted Solutions
Highlighted
Moderator

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

@Harguna ,checkUntitled.pngUntitled.pngUntitled.png the below screenshots.

 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

View solution in original post


All Replies
Highlighted
Moderator

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

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Moderator

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

@Harguna ,check the below screenshot will help you.Untitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Six Stars

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

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?

 

Highlighted
Moderator

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

@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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Six Stars

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

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?

 

Highlighted
Eleven Stars

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

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.

Regards
Abhishek KUMAR
Highlighted
Six Stars

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

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

Highlighted
Eleven Stars

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

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

Regards
Abhishek KUMAR
Highlighted
Moderator

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

@Harguna ,checkUntitled.pngUntitled.pngUntitled.png the below screenshots.

 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog