Loop through all the records in the DB for every record.

Minhajuddin · Post by **Minhajuddin** » Fri May 25, 2007 2:42 pm

Hi there,

Please help me to fix this tricky situation.

I have a sequential file. A DB stage which I used to lookup. And a target file.
I want to compare every input record with all the records in the DB stage(Which is huge 17 million). To add to the complexity I need to parse the values from an input column and compare it with the columns. I don't know how to approach this problem. Can we do it using Buildops. I tried searching for buildop in the DS documentation and didn't find much useful information in it. It would be great if you guys help me out of this.

Thank you.

ray.wurlod · Post by **ray.wurlod** » Fri May 25, 2007 2:58 pm

Build stage probably won't help all that much.

What you need first is a specification. You can generate the computed comparison value in an upstream Transformer stage, before attempting the lookup/join/merge.

Is the comparison against one column in every row, or against more than one column? In the latter case you will probably need to supply the comparison value a number of times (generate multiple copies of it from the Transformer stage) so that you can match the search key columns.

Minhajuddin · Post by **Minhajuddin** » Fri May 25, 2007 3:48 pm

Which stage would work in this situation where I need to compare every record with all the records in the DB. And for every record I may find a dozen matches. I need to send these 12 matched records and the input source record to the output.

ray.wurlod · Post by **ray.wurlod** » Sat May 26, 2007 5:39 am

I think what you're seeking to do is a join, best accomplished with a Join stage. But I'm not sure, which I why I suggested that a specification is the first thing you need - I don't think you're sure either.

Minhajuddin · Post by **Minhajuddin** » Sat May 26, 2007 2:43 pm

Hi Ray,
I could have used a Join if I just had one condition to compare.
But the scenario I am in, is a little different.

I need to see if condition1 is true OR condition2 is true OR ........and so on.

I have almost 10 conditions. And as far as I know we can't use OR statements as key columns in a Join. I know it sounds a bit confusing. But the following example should make my problem clearer.

Say I have the input data in the following format.

Code: Select all


EmpID		EmpResAddress		     EmpOfficialaddr
===========================================

1		     HN 1:Bakers Street	     Of1:Hogwarts Ville
		
2		     HN 2:Share Street	      Of2:Walt Disney

I have similar data in my Database. Now I need to parse all the values and compare the values with all the possible permutations. Say I compare the first field in the office address (Of1) with the first field in the office address of the DB and I compare Of1 with the first field in the Home address of the DB too. And I do the same with the Residential address. So here It all gets complicated. I don't think that we can implement this with a transformer or a join. I think that BuildOp can do this. I don't know how. I need the help of all you experts in this.

Thanks a lot.
:D

ray.wurlod · Post by **ray.wurlod** » Sat May 26, 2007 4:08 pm

See what I mean? A specification makes it clearer what your intent is. A full specification would help you (though not us) even more.

Is the database Oracle or DB2? If so, you can do this using a sparse lookup in combination with user-defined SQL. Or you might pass the ten key values to a stored procedure via an ODBC Enterprise stage and have it return the result set of the SP. Or you could use user-defined SQL to construct the compound join condition.

Don't essay the Build stage route until you've eliminated all the easier possibilities; using a Build stage means that the organization will need to maintain/buy C++ expertise to maintain the code.

However, if it's addresses you're comparing, have you considered using QualityStage? With multiple match passes you could ascertain all discoverable combinations, even those with minor misspellings.

Minhajuddin · Post by **Minhajuddin** » Sun May 27, 2007 12:42 am

Hi Ray,

My backend DB is Oracle. I don't know what a specification is.
Can you shed some light on it.
And since I am just a participant. I could not see the whole message.
Anyway thanks for all you help.

Minhajuddin · Post by **Minhajuddin** » Mon May 28, 2007 12:25 pm

Please Help!

michaeld · Post by **michaeld** » Mon May 28, 2007 1:03 pm

Maybe you can optimize the compare part of this task. That is, do you really need to compare with every record or can you limit it somehow?

You can try doing the parsing in SQL if it helps you get a direct join with the lookup table.

You can do a sparse lookup if you can find a way to limit you result set with an SQL WHERE clause.

I would need more details to give you a less general answer.