| best method to self join in discoverer [message #164815] |
Sun, 26 March 2006 20:51  |
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
|
Member |
|
|
Hi all
Have a workbook based on the following set of folders:
Position
Assignment
Person
Position_Manager
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
subordinate_position_name,
subordinate_position_id,
assignment_id,
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
cheers
cameron
|
|
|
|
| Re: best method to self join in discoverer [message #263277 is a reply to message #164815] |
Wed, 29 August 2007 10:22  |
denmage
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?
Thanks,
Denis Perreault
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
