?
Solved

oracle query outer joins

Posted on 2012-04-04
8
Medium Priority
?
451 Views
Last Modified: 2012-08-13
select d.department_id,ds.payroll_id,
       ds.schedule_date,ds.start_time,ds.start_ampm,
       ds.end_time,ds.end_ampm,ds.total_hours,
         nar.short_description,
         s.site_name,s.city_name
  from emp_unexcld eu,
       department d,
       sites s,
         dept_staff ds,
       not_avail_reason nar
         ,blood_drives bd
where eu.department_id = d.department_id  
  and d.site_code = s.site_code
  and eu.payroll_id = ds.payroll_id
  and ds.department_id = d.department_id
  and ds.reason_code = nar.reason_code  
  and nar.schedule_required_flag = 'Y'
  and ds.site_code = s.site_code
  and ds.site_code = bd.site_code
  and ds.schedule_date = bd.drive_date
  and ds.schedule_date >= sysdate
  order by ds.schedule_date desc
---------------------------------

I am trying to convert this into the above. Optional is outer join

1.employee in heartland (emp_unexcld)
link department_id of emp_unexcld to department_id of department(department) in heartland optional
2. link site_code of department to site_code of sites , site optional
3. link payroll_id of emp_unexcld to payroll_id in dept_staff optional
4. link department_id of dept_staff to department_id of department optional
5. link site_code of dept_staff to site_code of sites optional
6. link schedule_date, site_code of dept_staff to drive_date,site_code of blood_drives drive_date optional.

I have the query that I have done and posted on the top. But I need help in the outer joins as I mentioned above.
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37806989
If I understand this properly, try the old outer join syntax.

>>link department_id of emp_unexcld to department_id of department(department) in heartland optional

change:
eu.department_id = d.department_id

to:
eu.department_id = d.department_id(+)

I might have it backwards with the (+) on the wrong side but it is easily switched if you don't get the correct results.


Just do the same for the rest of your joins.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 37807019
There are two ways to write outer joins in Oracle; the "old way" using (+) and the "new way" using the ansii standard.  Here's the old way, which has the limitation of being able to outer join from only one table, but this isn't an issue with your requirement:

select d.department_id,ds.payroll_id,
       ds.schedule_date,ds.start_time,ds.start_ampm,
       ds.end_time,ds.end_ampm,ds.total_hours,
         nar.short_description,
         s.site_name,s.city_name
  from emp_unexcld eu,
       department d,
       sites s,
         dept_staff ds,
       not_avail_reason nar
         ,blood_drives bd
where eu.department_id = d.department_id  (+)
  and d.site_code = s.site_code (+)
  and eu.payroll_id = ds.payroll_id
  and ds.department_id (+) = d.department_id
  and ds.reason_code = nar.reason_code  
  and nar.schedule_required_flag = 'Y'
  and ds.site_code = s.site_code (+)
  and ds.site_code = bd.site_code (+)
  and ds.schedule_date = bd.drive_date (+)
  and ds.schedule_date >= sysdate
  order by ds.schedule_date desc

The outer join symbol (+) appears after each of the outer join conditions for the column of the table you want to outer join to.
0
 
LVL 6

Author Comment

by:anumoses
ID: 37807033
ORA-01417: a table may be outer joined to at most one other table
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 37807043
All the advice on that error can be found here:
http://www.dba-oracle.com/t_ora_01417_a_table_may_be_outer_joined_to_at_most_one_other_table.htm


If you must have that join, create a view that does the outer jpin and use that view in the main select.
0
 
LVL 6

Author Comment

by:anumoses
ID: 37807058
create or replace view off_duty (department_id,payroll_id,
       schedule_date,start_time,start_ampm,
       end_time,end_ampm,total_hours,
         short_description,
         site_name,city_name)
             as
select d.department_id,ds.payroll_id,
       ds.schedule_date,ds.start_time,ds.start_ampm,
       ds.end_time,ds.end_ampm,ds.total_hours,
         nar.short_description,
         s.site_name,s.city_name
  from emp_unexcld eu,
       department d,
       sites s,
         dept_staff ds,
       not_avail_reason nar
         ,blood_drives bd
where eu.department_id = d.department_id  (+)
  and d.site_code = s.site_code (+)
  and eu.payroll_id = ds.payroll_id
  and ds.department_id (+) = d.department_id
  and ds.reason_code = nar.reason_code  
  and nar.schedule_required_flag = 'Y'
  and ds.site_code = s.site_code (+)
  and ds.site_code = bd.site_code (+)
  and ds.schedule_date = bd.drive_date (+)
  and ds.schedule_date >= sysdate
  order by ds.schedule_date desc

---------------
same error
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37807136

Open in new window

>>same error

That looks like you tried to create a view with the ENTIRE select. That is not what the link talks about.

Take the our join that is causing the error, create a view between the two tables involved with an outer join.  Then use that new view in your main select.

The test below shows the problem and work-around.

drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1));

drop table tab3 purge;
create table tab3(col1 char(1), col2 char(1));

insert into tab1 values('a','1');
insert into tab1 values('b','2');
insert into tab2 values('a','3');
insert into tab3 values('a','3');
commit;

--fails since I use t2.col1 as an outer join twice.
select t1.col1, t1.col2, t2.col2, t3.col2
from tab1 t1, tab2 t2, tab3 t3
where t1.col1=t2.col1(+)
	and t3.col1=t2.col1(+)
/


--to fix:
-- outer join t2 and t3 in a view
create view t2_t3_vw as 
select t2.col1 t2_col1, t2.col2 t2_col2, t3.col2 t3_col2
from tab2 t2, tab3 t3
where t3.col1=t2.col1(+)
/

--then outer join t1 and the view
select t1.col1, t1.col2, t2_col2, t3_col2
from tab1 t1, t2_t3_vw
where t1.col1=t2_col1(+)
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 37807263
create or replace view off_duty (site_code,
                                 department_id,
                                                 payroll_id,
                                 schedule_date,
                                                 start_time,
                                                 start_ampm,
                                 end_time,
                                                 end_ampm,
                                                 total_hours,
                                 short_description)
             as
select d.site_code,d.department_id,ds.payroll_id,
       ds.schedule_date,ds.start_time,ds.start_ampm,
       ds.end_time,ds.end_ampm,ds.total_hours,
         nar.short_description
  from emp_unexcld eu,
       department d,
       dept_staff ds,
       not_avail_reason nar
where eu.department_id = d.department_id  (+)
  and eu.payroll_id = ds.payroll_id
  and ds.department_id (+) = d.department_id
  and ds.reason_code = nar.reason_code  
  and nar.schedule_required_flag = 'Y'
  and ds.schedule_date >= sysdate
  order by ds.schedule_date desc
 
select  a.department_id,a.payroll_id,
       a.schedule_date,a.start_time,a.start_ampm,
       a.end_time,a.end_ampm,a.total_hours,
         a.short_description,s.site_name,s.city_name
from off_duty a, sites s,blood_drives bd
where a.site_code = s.site_code(+)
and a.site_code = bd.site_code(+)
and a.schedule_date = bd.drive_date(+)      

-------------------This worked
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 37807330
thanks
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question