Looping help needed

Example: Let’s say I call a program and this programs returns me back two tables: PRECLAIM_TAB1 and PRECLAIM_TAB2. Now, let’s say that I am supposed to look for a field in PRECLAIM_TAB1 and verify that this same field is in PRECLAIM_TAB2 and if so, map certain fields from both tables to a canonical.

I am looping through entire tables looking for a line item match. Sometimes the field that I’m searching for is totally out of order, like below (1st column is line item num from table1, 2nd column is from table2):

100 500

200 200

300 400

400 300

500 100

If not for the possibility of the above happening, I wouldn’t have to do the very timeconsuming code of looping through everything on the PRECLAIM_TAB2 side until a match is found, then go to the next line item number (ex. 200), then loop through the entire PRECLAIM_TAB2 side looking for 200, and on and on.

My issue: The way I’m doing the mapping is very timeconsuming (especially if there’s a large amount of data), and I’m wondering if there’s a better and more efficient way to handle this.

To put it in a nutshell, I grab the line item number field (starting at top - say 100) in PRECLAIM_TAB1, loop over the entire PRECLAIM_TAB2 side until a 100 is found in the line item number field, and if a match is found, data from both tables are mapped to a canonical.

Is there anything in addition that I could do (or a different concept entirely) to make this concept work faster or is there no other way to do this, other than what I stated.


Convert the doc you are searching iteratively to a node and then use queryXMLNode with an XQL query that you generate programatically and map in. No looping, fast searching.