topic Re: [resolved] Converting WHERE clause in Talend Studio

[resolved] Converting WHERE clause

Anonymous — Tue, 24 May 2016 14:52:41 GMT

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?

Re: [resolved] Converting WHERE clause

vivekjayapalan — Tue, 24 May 2016 15:01:00 GMT

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.

Re: [resolved] Converting WHERE clause

Anonymous — Tue, 24 May 2016 15:02:12 GMT

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.

Re: [resolved] Converting WHERE clause

Anonymous — Wed, 25 May 2016 11:13:39 GMT

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?

Re: [resolved] Converting WHERE clause

Anonymous — Wed, 25 May 2016 12:16:50 GMT

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.

Re: [resolved] Converting WHERE clause

Anonymous — Thu, 26 May 2016 08:05:50 GMT

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

Re: [resolved] Converting WHERE clause

Anonymous — Thu, 26 May 2016 09:30:02 GMT

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.