topic Re: Filtering data from Oracle and SQL Server in Talend Studio

Filtering data from Oracle and SQL Server

Anonymous — Sat, 16 Nov 2024 04:23:26 GMT

Hi all,

I'm trying to update data from an Oracle Database to a SQL Server Database.
If data is already registered to SQL Server, i don't want to register this data again.

I did my workspace like this : (First capture)

I configured my TMap like this : (Second capture)

I think my mistake is the Inner Join. I don't know if i'm doing it well.

Any help is greatly appreciated.

Regards.

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 14:58:39 GMT

Can you share your lookup table settings including thé SQL query?

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 15:02:21 GMT

Sure.

Annotation 2019-10-14 160122.png
Annotation 2019-10-14 160155.png

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 15:09:42 GMT

Did you try to replace the tMap lookup model by "load once" (check the name of the option)?

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 15:17:39 GMT

Yep, but did nothing better.

I don't know if the problem come from the join between my 2 tables. Cause all my data returned from row2 (check the second screen from my first topic) are null. But there is no null value in my table.

I tried every Lookup Model, every Join Model, every Match Model from row2.

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 15:33:55 GMT

So, tDBInput_1 should be related to Oracle database and tDBInput_2 to SQL Server.

In the tMap, change "Recharger à chaque ligne" by "Load once" and change "Catch output reject" to false on the output flow (tMap right side).

It should work like this.

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 15:38:42 GMT

"So, tDBInput_1 should be related to Oracle database and tDBInput_2 to SQL Server."

It's true.

I tried your params, but didn't work.

Again Null values for all my table sql server.

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 15:53:43 GMT

What do you mean with "Again Null values for all my table sql server"?

Null value for H_DATEFIN is not required as soon as you have it into the where clause.
For the rest, no problem.
Maybe could you share again the last version of your tMap.

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 15:58:47 GMT

first screen is TMap config update.

Second screen is what my table look like.

What i was trying to explain is :"after the TMap, NIVNATLIBL_1 is always null instead of ... "

Annotation 2019-10-14 165440.png
Annotation 2019-10-14 165441.png

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 16:09:51 GMT

Sorry, my mistake!

SQL Server must be the main table and Oracle the lookup.

Don't change anything else to get records existing into SQL Server but not into Oracle (thank's to the option "catch inner join reject").

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 16:20:43 GMT

Same problem but now sql server table has values, oracle table is full of null values.

I've a primary key on Nivnat from oracle but not on nivnat from sqlserver. Nivnat is the link for my join. if my sql server table is main, my join can't work ?

Check my new TMAP config.

Annotation 2019-10-14 172249.png

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 16:25:56 GMT

"oracle table is full of null values" it's normal as soon as you catch only inner join rejects.
This option means you want to get only existing record in SQL Server which don't exist in Oracle - so it doesn't make sense to try to get values from this table.

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 16:28:46 GMT

"This option means you want to get only existing record in SQL Server which don't exist in Oracle"

- But i want to get only existing record in Oracle which don't exist in SQL Server

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 16:38:10 GMT

Sorry again.
So your main table should be Oracle and SQL Server the lookup. tMap options are right (catch only rejected records).

Re: Filtering data from Oracle and SQL Server

Anonymous — Mon, 14 Oct 2019 16:38:24 GMT

I don't know if i'm using the good method.

I want to ckeck line by line from my oracle table.

with my primary key from oracle table, i want to find the good line in my sql server table.

with my filterrow, i compare every field to check if there is the same values or not.

if it's not the same, i save, if it's, i skip.

Re: Filtering data from Oracle and SQL Server

TRF — Mon, 14 Oct 2019 20:17:41 GMT

If you want to compare every fields between both tables, you can build the tMap inner join with all fields if datatypes are identical.
Thanks to "catch inner join reject" as soon as you have a difference for one field, the record will be rejected ans you will be able to do what you want with the target table.

Re: Filtering data from Oracle and SQL Server

Anonymous — Tue, 15 Oct 2019 11:06:35 GMT

So, if i understand well, this one should work well ? or did i have to put every columns from row1 to row2 like row1.nivnat ?

Annotation 2019-10-15 120452.png

Re: Filtering data from Oracle and SQL Server

TRF — Tue, 15 Oct 2019 12:14:46 GMT

With this one, you'll catch only records from row1 where NIVNAT doesn't match with any value from row2 or H_DATEFIN is not null.
If you want to catch all records from row1 where at least one of the 6 fields doesn't match with the corresponding one from row2, you need to do what you do for NIVNAT for all the fields.
Also, remember that as soon as you are using an inner join and decide to catch "inner join rejects", all fields populated from row2 will remain null. That's make sense because you'll have 1 output record for each record from row1 which doesn't have correspondance in row2. Right?

Re: Filtering data from Oracle and SQL Server

Anonymous — Tue, 15 Oct 2019 13:10:25 GMT

@TRF wrote:
With this one, you'll catch only records from row1 where NIVNAT doesn't match with any value from row2 or H_DATEFIN is not null.

Ok.

@TRF wrote:
If you want to catch all records from row1 where at least one of the 6 fields doesn't match with the corresponding one from row2, you need to do what you do for NIVNAT for all the fields.

I did it like this : (understand how to put image into my text )

@TRF wrote:
Also, remember that as soon as you are using an inner join and decide to catch "inner join rejects", all fields populated from row2 will remain null. That's make sense because you'll have 1 output record for each record from row1 which doesn't have correspondance in row2. Right?

I'll show you my 2 tables, may be easier to understand :

MSSQL :

Oracle :

My Inner join should give me 5th and 6th line cause they are unregistered in my mssql ?

The return in the black circle should be 2 ?

Re: Filtering data from Oracle and SQL Server

TRF — Tue, 15 Oct 2019 13:36:11 GMT

I think you've got 0 row because of date fields which are different from Oracle and SQL Server. I suggest you to use the appropriate conversion in the SQL queries to get both as text with the same format.