oracle query outer joins

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.
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:

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
 
slightwv (䄆 Netminder) Commented:
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
 
MilleniumaireCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anumosesAuthor Commented:
ORA-01417: a table may be outer joined to at most one other table
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.