I’m trying to execute an SQL statement with where clause as follows :
A.A1 = B.B3
AND A.A1 = C.C1
AND C.C3 = B.B4
I’m trying to execute the query in Talend by using an tMSSqlInput for each table and then joining the 3 inputs using a tMap.
I’m struggling on where to put/implement the where clauses.
Any help, please ?
Why don't you copy the whole SQL statement into the Query field and enclose the statement with double quotes? Then click on Guess Schema. The component will run the Query and Guess the Schema from it.
if you can't run the SQL as one query (as iburtally suggested), the joins/where clauses are implemented in a tMap on the input panel (left side) by connecting the corresponing columns.
Which other way you want to do it?
When doing ETL, there are 2 things: If it is easier and faster to do a query with complex joins as SQL for the DB, then you should do that and avoid pulling out millions of records out of DB, onto the network, onto the JobServer, into a Java Process memory for joining. If you are using a transformation function that the DB does not provide, then you need to do that and your need to design that type of join so that it performs.
Yes I tried it using the tMap, but it doesn't work. There is a problem with the way in which I connect them in the tMap. Do you have any link or representation which illustrates clearly how to implement where clauses in tMap ?
The point of the tMap is not to replicate all the join capabilities that a Database can do. We should not re-invent the wheel here. If it is easier and faster done using SQL pushed down to the DB, then we should leverage that.
Having said that, if you want to do this in tMap, you will need 1 main data, from table A, and 2 lookups for table B and C, and then do the join in the tMap. You need to figure out whether each join is Inner Join or Left Outer Join to simulate the same SQL logic you are doing, and you may need to do additional steps like filtering and removing duplicates. The tMap provides join functionality that works for DB as well as non db sources.