Solved

oracle query outer joins

Posted on 2012-04-04
8
450 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 77

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 77

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

695 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