Solved

oracle query outer joins

Posted on 2012-04-04
8
444 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
  • 4
  • 3
8 Comments
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

770 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