Solved

oracle query outer joins

Posted on 2012-04-04
8
435 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)
Comment Utility
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
Comment Utility
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
Comment Utility
ORA-01417: a table may be outer joined to at most one other table
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
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 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 65
SQL query of Oracle 10g database. 8 56
Oracle TEXT search question 9 26
statspack purge automate 7 27
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now