Outer joining

[suman123]

I have this SQL :


---------------

select
e.emp_val1 as location,
e.emp_val3 as dept,
e.emp_val2 as clock,
e.emp_lastname || ', ' || e.emp_firstname as name,
ws1.wrks_udf1 as rate1,
ws2.wrks_udf1 as rate2,
ws3.wrks_udf1 as rate3,
ws4.wrks_udf1 as rate4,
ws5.wrks_udf1 as rate5,
ws5.wrks_udf1 as rate6,
( (
ws1.wrks_udf1 +
ws2.wrks_udf1 +
ws3.wrks_udf1 +
ws4.wrks_udf1 +
ws5.wrks_udf1 +
ws6.wrks_udf1
) /
(
decode (
(decode(ws1.wrks_udf1,'0',0,1) +
decode(ws2.wrks_udf1,'0',0,1) +
decode(ws3.wrks_udf1,'0',0,1) +
decode(ws4.wrks_udf1,'0',0,1) +
decode(ws5.wrks_udf1,'0',0,1) +
decode(ws6.wrks_udf1,'0',0,1)),'0',1, (decode(ws1.wrks_udf1,'0',0,1) +
decode(ws2.wrks_udf1,'0',0,1) +
decode(ws3.wrks_udf1,'0',0,1) +
decode(ws4.wrks_udf1,'0',0,1) +
decode(ws5.wrks_udf1,'0',0,1) +
decode(ws6.wrks_udf1,'0',0,1))
)
)
) as avgrate

from
employee e,
pay_group pg,
work_summary ws1,
work_summary ws2,
work_summary ws3,
work_summary ws4,
work_summary ws5,
work_summary ws6

where
e.emp_val1 = #request.dbLocation#
and e.emp_val1 = pg.paygrp_name
and ws1.emp_id = e.emp_id
and ws2.emp_id = e.emp_id
and ws3.emp_id = e.emp_id
and ws4.emp_id = e.emp_id
and ws5.emp_id = e.emp_id
and ws6.emp_id = e.emp_id
and pg.paygrp_end_date-42 = ws1.wrks_work_date
and pg.paygrp_end_date-35 = ws2.wrks_work_date
and pg.paygrp_end_date-28 = ws3.wrks_work_date
and pg.paygrp_end_date-21 = ws4.wrks_work_date
and pg.paygrp_end_date-14 = ws5.wrks_work_date
and pg.paygrp_end_date-7 = ws6.wrks_work_date

---------------


I want to do an outer join on the where clause like this :


where
e.emp_val1 = #request.dbLocation#
and e.emp_val1 = pg.paygrp_name
and ws1.emp_id = e.emp_id(+)
and ws2.emp_id = e.emp_id(+)
and ws3.emp_id = e.emp_id(+)
and ws4.emp_id = e.emp_id(+)
and ws5.emp_id = e.emp_id(+)
and ws6.emp_id = e.emp_id(+)

but Oracle complains that I cannot do an outer join from one table to more than one table. I dont want the outer join on the 'ws' side (left side). I want it on the right side as I have shown above.

Is there anyway to accomplish this ? Or maybe rewrite the query to accomplish it another way ?

Any help is greatly appreciated !

[slash]

Hi you might have to try some workaround like

select...
where and ws1.emp_id = e.emp_id(+)
union
select...
and ws2.emp_id = e.emp_id(+)
union
select...
and ws3.emp_id = e.emp_id(+)
and so on

Cheers
Slash

[abhaysk]

try


from
(Select ws.(Primary_Col_List),Emp.* from employee Emp, work_summary ws Where ws.emp_id=emp.emp_id(+)) e,
pay_group pg,
work_summary ws1,
work_summary ws2,
work_summary ws3,
work_summary ws4,
work_summary ws5,
work_summary ws6
where
nvl(e.emp_val1,#request.dbLocation#) = #request.dbLocation#
and nvl(e.emp_val1,pg.paygrp_name) = pg.paygrp_name
and ws1.emp_id = NVL(e.emp_id,ws1.emp_id) and e.(cols of ws)=ws1.(cols of pk)
and ws2.emp_id = NVL(e.emp_id,ws2.emp_id) and e.(cols of ws)=ws2.(cols of pk)
and ws3.emp_id = NVL(e.emp_id,ws3.emp_id) and e.(cols of ws)=ws3.(cols of pk)
and ws4.emp_id = NVL(e.emp_id,ws4.emp_id) and e.(cols of ws)=ws4.(cols of pk)
and ws5.emp_id = NVL(e.emp_id,ws5.emp_id) and e.(cols of ws)=ws5.(cols of pk)
and ws6.emp_id = NVL(e.emp_id,ws6.emp_id) and e.(cols of ws)=ws6.(cols of pk)
and pg.paygrp_end_date-42 = ws1.wrks_work_date
and pg.paygrp_end_date-35 = ws2.wrks_work_date
and pg.paygrp_end_date-28 = ws3.wrks_work_date
and pg.paygrp_end_date-21 = ws4.wrks_work_date
and pg.paygrp_end_date-14 = ws5.wrks_work_date
and pg.paygrp_end_date-7 = ws6.wrks_work_date


Abhay.