How to avoid auto join in Catalogue

[dm21]

Hi

I would like know if there is any good solution to following situation.

I have three tables -
Account , Employee_Team and Tdata
with three conditions
1. where account.account_cd = Tdata.account_cd
and account.employee_team_id = employee_team.employee_team_id
2. Where Tdata.trader_id = employee_team.employee_team_id
3. where Tdata.employee_team_id
= employee_team.employee_team_id

Now if I force the join between Account and Employee_team in my catalogue the situation 2 and 3 will not work.
I tried using alias and views for account and Employee_Team still it does not work becausee original account table and alias account table is joined to Tdata table. And if I query on original Account table it auto joins Employee_Team table through Tdata Table.

Right now I have manual joins in query for all this condition. I wonder if there is any other solution to this situation.

Your suggestions are greatly appreciated.

Thanks.

 

[CanadianTechie]

I'm not sure I understand the question -- manually keying in the SQL join detail should solve your problem, and avoid the auto-join scenario you describe...why isn't this a suitable solution for you?



Pedro Cardoso
A.K.A CanadianTechie

 

[dm21]

Pedro,

The reason I have to do the manual join is because if I join Account table with the Employee Team table in catalogue, the second and third condition will not work. Where you can see Tdata join is directly with Employee Team where as in first one it connects via Account table.

I hope this is clear to you.

DM

 

[griffindm]

DM,

Not clear, really. What is needed is an explanation of the character of the joins. We need to understand what the purpose of each join is. This is so we can determine whether the multiple joins between Tdata and Employee are part of a single join, or where Employee is aliased in order to do a second join to that table.

Please explain.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[dm21]

Here is the explanation
1. where account.account_cd = Tdata.account_cd
and account.employee_team_id = employee_team.employee_team_id

This joins Tdata table to Employee Team table via Account code table. This gives me all the employee who covers the trade.

2. Where Tdata.trader_id = employee_team.employee_team_id

This Joins Tdata table to employee Team table. This gives me all the employees who booked the trade.
This condition should not have Account table to join.
because employee who booked the trade does not need to cover the account.

3. where Tdata.employee_team_id
= employee_team.employee_team_id

This Joins Tdata table to employee Team table. This gives me all the employees for historical Coverage.
Even this condition should not have Account table to join because this implies that that employee used to cover that account before and is not covering now.

Now, if I do a join in Catalogue like below
Tdata --> Account --> Employee Team
only who covers i.e. the first condition will work.

Now if do alias for Account and Employee Team i.e
Tdata --> Account(alias)
Tdata --> Employee_team
If I query on Account(alias) and Tdata table it includes Account table too.

See the following example.
select T1.account_cd c1,
T2.employee_team_id c2
from TDATA T3,
ACCOUNT T1,
ACCOUNT_WHO_COVERS T4, (alias)
EMPLOYEE_TEAM_WHO_COVERS T5, (alias)
T_EMPLOYEE T6,
T_EMPLOYEE_TEAM T2
where T3.account_cd = T1.account_cd
and T4.account_cd = T3.account_cd
and T4.employee_team_id = T5.employee_team_id
and T5.employee_id = T6.employee_id
and T2.employee_id = T6.employee_id

I hope I have not confused you guys more.

I really appreciate your help.

Thanks
DM