Joining 2 Databases - Multiple Times 1

[hormy83]

I've got two databases, I will simplify the sample data to make it easier to explain and understand. One database has money paid to an individual and who their boss is. This way we can create reports for each boss showing only what their employees make. Then in another database we have the names of all the people in the company plus other information we will need.

So for example we would have data returned like this:

Boss ID     Emp ID     $
003         002        $5
003         005        $4
001         008        $3
001         007        $5

And in the second database it will have:

ID      Name
001     Joe
002     Kathy
003     Evan
005     Erin
007     Sue
008     Bob

So I have both of these Data Sources in a Multiple Input filter. I am able to sort the first by EmpID and the second by ID and join them to get a row (by overriding the Fetch method) that has the Employee's name for each row. But now I also need to loop through again and get the manager's name.

I would like to accomplish this without having to re-run the second query again. About the only thing I can think of is when I loop through each to make the joins I'll also stick all the results from query 2 into an array and then when at the end I will loop back through data from query 1 and join it to data from query 2 using the array that contains all the info from query 2.

My concern with that is query 2 could be returning 50,000 rows, so this may not be the best use of resources.

 

[ActuateConsultant]

Instead of querying twice, or loading an array, try using a RecordBuffer. See the example "ReuseQuery" at the following location:
C:\Program Files\Actuate8\eRDPro\Examples\DataAccess\ReuseQuery