Hi, I currently have a SQL query that I need to put into a Talend job. This query has 2 tables, table A and B and the WHERE clause in the query is as follows WHERE TableA.Field_1 = TableB.Field_1 AND (TableA.Field_2 <> TableB.Field_2 OR TableA.Field_3 <> TableB.Field_3) How can I translate this into a Talend job?
If your source database is Oracle, just use tOracleInput and write ur customs query in the query section and click guess schema. Make sure you specify field names in your select. Also leave the table name option blank ("") in your tOracleInput component, the table names specified in your query should be good to select the data.
Just copy and paste your SQL query into a tOracleInput component, wrap in double quotes and configure your schema to be exactly the same as your "Select". Basically the t{Database}Input components just fire whatever SQL query you need to your database. The query is wrapped in quotes because it is essentially a Java String. This gives you the advantage of being able to dynamically change the query at runtime.
Thanks but the join I am creating in the job is between an Oracle database table and MSSql database table.
So writing a query in the t[Database]Input component will not work.
I need to use a tMap to join the data from these two tables.
Will writing the WHERE clause fields in the Filter expression of the lookup table work?
OK, that was an important bit of information that was left out of the question. In which case, query using a tOracleInput and tMSSQLInput. The Oracle component for the Oracle table and the MSSQL component for the MSSQL table. I advise filtering your data as much as you can here. Then use a tMap to carry out the join conditions.
@rhall_2.0:
What if the filtering done needs to compare the fields from both these tables?
In this case the query in the tOracleInput and tMSSqlInput components would not have a WHERE clause.
So is it a good idea not to make a join between these two tables in the tMap component.
But put a filter expression that uses the fields of both these tables in the Filter expression box of the incoming row that is designated as lookup?
To be honest, if you cannot restrict rows in your SQL there is little point putting a filter in. You may as well put the filtering logic into the tMap. The tMap will allow you to restrict output rows based on inner joins or outer joins. The only reason I suggested filtering data in your SQL queries is that a lot of the time people just dump everything to the Talend job and filter there. This is incredibly inefficient if you can reduce your dataset at the database end. A database will filter data much quicker than Java.
