Talend community forum / tMap lookup

vital_parsley · 2011-12-19 13:21:45

Tags: [database, development, lookup, oracle, schema, tmap]

Hi,

I have a small scenario where i want to fetch values from a lookup table.
I have gone through some previous posts here to find a solution but could not find one.

So the scenario is :

I have 2 tables src and tgt

when i insert data into tgt from src i want to check if that particular value already exists in the tgt or not .If the value exists in the tgt then i would not insert the src row into the tgt else i will insert into tgt.

For this i thought i could use a left outer join and check if the value that i have got from lookup table is null or not if it is null then value does not exists in tgt else it exists.

How can i achieve this ?
Please help !

Or3l · 2011-12-19 13:44:24

Hi,
Just do an inner join in your tmap and catch the reject rows.

vital_parsley · 2011-12-19 14:51:38

Hi i did what u said.

And it seemed fine to be working for the first time.

e.g

When i ran for the first time :
tgt table had no rows so all rows from src got inserted into tgt.Which even reflected in the DB
Now from DB i deleted the table as in delete from src

Now again i ran the mapping and this time it did not insert any rows .
So i checked the tOracleInput(which i have for tgt to lookup) here when i fire the Query and check it shows all the rows which i had deleted from DB.
Why is that not getting reflected when i run the components again ???

Or3l · 2011-12-19 15:44:29

Can you post a picture of your job?

janhess · 2011-12-19 16:01:14

Did you commit your database changes after the delete?

vital_parsley · 2011-12-19 16:24:47

i m trying to post the pics .But the pics are not getting uploaded.Do not understand the reason.

i committed it to the database.

tgt
|
|
src----->tMap-->tgt-->tOracleCommit

Here,i will try again

Last edited by vital_parsley (2011-12-19 16:33:05)

vital_parsley · 2011-12-19 16:42:46

Hi,
I m attching my job and tMap view in 2 pics

Uploaded Images

Last edited by vital_parsley (2011-12-19 16:44:25)

vital_parsley · 2011-12-19 16:53:13

Here even though there is no data in the tgt still when i check the tOracleInput(GRADE_CODE_MAP) and execute Query in Talend SQLBuilder i get rows that i had already deleted from DB.
The Query Type here is Built-In

janhess · 2011-12-19 16:54:45

vital_parsley wrote:
Hi i did what u said.

So i checked the tOracleInput(which i have for tgt to lookup) here when i fire the Query and check it shows all the rows which i had deleted from DB.
Why is that not getting reflected when i run the components again ???

Did you commit the change after doing the delete? The rows can't magically reappear in your table.

vital_parsley · 2011-12-19 17:05:08

i know that theres no magic!

I m just asking if u can figure out from the pictures what i m doing wrong.I think in the job it shows i m using a tOracleCommit to commit chnages.If that is how a commit is done.

What Query type should be kept ? built - In or Repository ?
Please let me know if any other pics u want me to post to figure out the problem.

vital_parsley · 2011-12-19 17:56:06

How to commit the connection ??

I have an tOracleCommit but the Component List is empty ?

I have connected the tOracleCommit to tgt using On Component OK

janhess · 2011-12-19 18:02:17

I think you're missing the point. Your problem is with deleting the records. Since they weren't deleted you can't insert them again.

vital_parsley · 2011-12-19 18:36:22

Ok.Thanx for all the help !Appreciate it

I have got it sorted out.

But i would like to know how to commit a transaction ?

Because when i run the job i see rows passed on to the tgt but when i check it in DB i dont see the rows inserted.

So,how do i commit ?

Or3l · 2011-12-19 21:44:31

Hi,
Put a tOracleConnection at the begining of your job and use this connection in your tOracleCommit.

vital_parsley · 2011-12-19 21:54:37

fINE WILL DO THAT.tHANX FOR UR REPLY.

Announcement

Unanswered posts

#1 2011-12-19 13:21:45

tMap lookup

#2 2011-12-19 13:44:24

Re: tMap lookup

#3 2011-12-19 14:51:38

Re: tMap lookup

#4 2011-12-19 15:44:29

Re: tMap lookup

#5 2011-12-19 16:01:14

Re: tMap lookup

#6 2011-12-19 16:24:47

Re: tMap lookup

#7 2011-12-19 16:42:46

Re: tMap lookup

#8 2011-12-19 16:53:13

Re: tMap lookup

#9 2011-12-19 16:54:45

Re: tMap lookup

vital_parsley wrote:

#10 2011-12-19 17:05:08

Re: tMap lookup

#11 2011-12-19 17:56:06

Re: tMap lookup

#12 2011-12-19 18:02:17

Re: tMap lookup

#13 2011-12-19 18:36:22

Re: tMap lookup

#14 2011-12-19 21:44:31

Re: tMap lookup

#15 2011-12-19 21:54:37

Re: tMap lookup

Board footer