Rules in transformer for deleting rows

pascalnicolasl · Post by **pascalnicolasl** » Wed Sep 16, 2009 4:03 am

Hello,

I have 2 stages (each one is retrieving data from a different table in a different Oracle 10g Database).

Stage 1 : Database A
Stage 2 : Database B

My goal is to remove invoice number in Database A if invoice number has already been deleted in Database B.

I have tried to implement some rules in my transformer, but I did not have any success till now.

Any hints are most welcome.

Thanks,
Nick

chulett · Post by **chulett** » Wed Sep 16, 2009 6:47 am

OK... does 'deleted in Database B' mean actually gone / deleted / no longer there or is it more of a logical deletion by setting a flag? Assuming the former, worst case you could build a reference hashed file of B invoices and then stream them in from A, sending a delete transaction back to A if the lookup fails.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 16, 2009 5:24 pm

Can you link the databases in Oracle and use SQL?

pascalnicolasl · Post by **pascalnicolasl** » Wed Sep 23, 2009 5:49 am

Yes, I mean that invoice number was deleted completely in Database B.

pascalnicolasl · Post by **pascalnicolasl** » Wed Sep 23, 2009 5:51 am

Hello Craig,

Can you elaborate further on your ideas?

Your solution seems to suit my needs.

Thanks,
Nicolas

chulett · Post by **chulett** » Wed Sep 23, 2009 6:56 am

Not really sure how I could elaborate much on it. Build a hashed file with all of the invoices from B. Stream in invoices from A and do a lookup to the B hashed file, when your lookup fails you need to delete the A invoice. I'd probably do the actual deletion in a separate step by landing the keys to delete first, rather than deleting from the same table you are sourcing from, which could have... issues.

pascalnicolasl · Post by **pascalnicolasl** » Thu Sep 24, 2009 1:43 am

How to do make the "lookup" ?

Thanks,
Nick

chulett · Post by **chulett** » Thu Sep 24, 2009 5:35 am

Have you not worked with hashed files yet? They are the heart and sould of Server jobs so something to become very, very familiar with if you haven't already.

Probably easiest to do this in two jobs. First one that just creates the hashed file and looks something like:

OCI -> Transformer -> Hashed File

That one sources from B. Then a second job sources from A and in the transformer does a reference lookup to the above hashed file.

laknar · Post by **laknar** » Thu Sep 24, 2009 10:52 pm

why dont you use simple SQL query to delete the records in DatabaseA which is NOT IN DatabaseB.

chulett · Post by **chulett** » Fri Sep 25, 2009 7:06 am

Well, the question at hand was how to do it in a job so that's the question I answered. And not quite so straight forward if the tables are really in two databases, as was alleged here.