Oracle8 multiple nested JOINS

[Pontiphex]

Ok, I am migrating a database from SQL Server 7.0 to Oracle8.0.5.1 Enterprise Edition on Linux. I have all the tables moved over, and now I am working on the various views that were part of the SQL Server schema.<br>
<br>
I have come across a massive view built from about 8 JOINS (inner, outer, and left outer) and of course SQL Server supports keywords like 'INNER JOIN' and 'LEFT OUTTER JOIN' which Oracle does not have. So I need some help with figuring out these joins. (I have been reading on the subject for 3 days and have bought about $130 worth of books on SQL and Oracle SQL and still am unsure of how to do this)<br>
<br>
Here is the original select statement that defines the view from SQL Server:<br>
<br>
SELECT a.Company_RecID, <br>
a.Activity_RecID, <br>
a.Category_ID, <br>
a.Date_Entered, <br>
a.Urgency_ID, <br>
a.Activity_Action_ID, <br>
aa.Description, <br>
tm.Assign_To, <br>
tm.Assigned_By, <br>
a.Activity_Board_RecID, <br>
aa.Sort_Order, cc.Short_Name, <br>
c.Company_ID, <br>
tm.To_List_Flag, <br>
tm.By_List_Flag, <br>
a.Subject, <br>
tm.Default_Flag, <br>
tm.Priority, <br>
a.Date_Required, <br>
a.Closed_Flag, <br>
a.Project_RecID, <br>
Project.Project_ID, <br>
Activity_Urgency_Code.Color, <br>
Activity_Urgency_Code.Description AS UC_Desc, <br>
Activity_Urgency_Code.Sort_Order AS UC_sort_order, <br>
a.First_Name, <br>
a.Last_Name, <br>
c.Company_Name<br>
FROM Activity a INNER JOIN<br>
Activity_Action aa ON <br>
a.Activity_Action_ID = aa.Activity_Action_ID <br>
INNER JOIN Task_Manager tm ON <br>
a.Activity_RecID = tm.Activity_RecID <br>
INNER JOIN Company_Category cc ON <br>
a.Company_RecID = cc.Company_RecID AND <br>
a.Category_ID = cc.Category_ID <br>
INNER JOIN<br>
Company c ON a.Company_RecID = c.Company_RecID AND <br>
cc.Company_RecID = c.Company_RecID <br>
LEFT OUTER JOIN<br>
Activity_Urgency_Code ON <br>
a.Urgency_ID = Activity_Urgency_Code.Urgency_ID <br>
LEFT OUTER JOIN<br>
Project ON a.Project_RecID = Project.Project_RecID<br>
<br>
So now how do I go about recreating this in the SQL that Oracle supports. From what I have learned so far, I am gonna try and create several smaller views to build upon to create the final view.<br>
<br>
What is more performance oriented? Dumping all the SQL in one view and letting the optimizer figure it out, or creating multiple smaller views and joining those views together to create a larger view?<br>
<br>
Thanks in advance for the help,<br>
--Bryan

 

[MikeLacey]

As far as I'm aware Oracle does INNER joins by default (please feel free to jump in here and correct me anyone) but outer joins are done like this:<br>
<br>
left outer<br>
<br>
where t1.f1(+) = t2.f1<br>
<br>
and right outer joins:<br>
<br>
where t1.f1 = t2.f1(+)<br>
<br>
Mike<br>

 

[Pontiphex]

Yeah I figured it out. It turns out that you do not need to nest the joins like SQL Server does. You can just throw in all of your joining parameters and Oracle figures it out. So the original code from SQL Server (in the original post) ends up in Oracle being:<br>
<br>
select a.company_recid,<br>
a.activity_recid,<br>
a.subject,<br>
a.category_id,<br>
a.closed_flag,<br>
a.first_name,<br>
a.last_name,<br>
a.date_entered,<br>
a.urgency_id,<br>
a.date_required,<br>
a.activity_action_id,<br>
a.activity_board_recid,<br>
a.project_recid,<br>
aa.description,<br>
aa.sort_order,<br>
p.project_id,<br>
auc.color,<br>
auc.description as uc_desc,<br>
auc.sort_order as uc_sort_order,<br>
tm.priority,<br>
tm.assign_to,<br>
tm.assigned_by,<br>
tm.default_flag,<br>
tm.to_list_flag,<br>
tm.by_list_flag,<br>
c.company_id,<br>
c.company_name,<br>
cc.short_name<br>
from activity a,<br>
activity_action aa,<br>
project p,<br>
activity_urgency_code auc,<br>
task_manager tm,<br>
company c,<br>
company_category cc<br>
where (a.activity_action_id = aa.activity_action_id)<br>
and (a.project_recid = p.project_recid(+))<br>
and (a.urgency_id = auc.urgency_id(+))<br>
and (a.activity_recid = tm.activity_recid(+))<br>
and (tm.default_flag = '1')<br>
and (a.company_recid = c.company_recid)<br>
and ((cc.category_id = a.category_id) <br>
and (cc.company_recid = a.company_recid)<br>
and (cc.company_recid = c.company_recid))<br>
<br>
So the oracle where clause seems much easier to follow than the nested structure of SQL Server (or the nature of the SQL-2 standard).<br>
<br>
One largely contributing factor to my problems is that I had corrupted data in one of my tables from when I migrated the tables from SQL Server -&gt; Oracle8/Linux. I actually came up with the working SQL statement on my first try, but since I got back a huge cartesian plot I figured I was going down the wrong path....it wasn't until about 2 days later I decided to inspect all of the data in each table (which is alot of data).<br>
<br>
Oh well.