Home » Developer & Programmer » Reports & Discoverer » best method to self join in discoverer
best method to self join in discoverer [message #164815] Sun, 26 March 2006 20:51 Go to next message
Messages: 33
Registered: November 2005
Location: Australia
Hi all

Have a workbook based on the following set of folders:


where position has an outer join to assignment and assignment has a one to one join with person, and position joins to position_manager to give manager for that position (join position.position_id = position_manager.subordinate_position_id). All are date tracked, and as the workbook is meant to be at an effective date, all three folders in the workbook have conditions placed on them (effective date between start and end date).

Works fine, workbook returns the following rows

employee_name, (in subordinate position)
manager_position_id (contains subordinate_position_id, parent_position_id, start_date, end_date)

What I need to do is include some extra information for the manager position, namely some data from from the position, assignment and person folders where position_manager.position_id = position.position_id.

My first thought was to replicate the position, assignment and person folder and joins between, then create a join between these copy folders and position_manager, however this decreased peformance to the magniture of 1000's.

Can somebody suggest any approach which may be more efficient than returning this? (eg a function, different approach with the folders / views).

any assistance / point in the right direction would be greatly appreciated. If any extra info would be helpful let me know



Re: best method to self join in discoverer [message #263277 is a reply to message #164815] Wed, 29 August 2007 10:22 Go to previous message
Messages: 2
Registered: August 2007
Junior Member
Wow, I can't believe I found a post by someone having the exact same problem that I am having.

I am trying to link several folders with criteria that crosses all of the folders. To date we have been able to do this in two ways.

The first is to put the criteria in a condition in the workbook. However, this means replicating the condition to all workbooks that need it. This could cause a maintenance nightmare down the road if anything changes.

The second is to merge all of the folders into a single folder that pulls all of the needed tables into the one folder. This method allows us to put the criteria into a single sql statement, but the downside it a significant loss of performance.

Does anyone know of a way to specify cross-folder criteria without having to resort to either of the two methods described above?


Denis Perreault
Previous Topic: discoverer issue
Next Topic: Message box interprets while sending mail from FORMS
Goto Forum:

Current Time: Thu Aug 17 10:29:13 CDT 2017

Total time taken to generate the page: 0.08703 seconds