Hi all,
Don't know what I am doing wrong but a full outer join does not return all rows.
if I change the tables to inline views it works.
- This query does not return all rows
select
y1.company_id,
customer_info_api.get_name(y1.company_id) name,
y1.part_no,
inventory_part_api.get_description('SN',y1.part_no),
y1.MAR "03-2003",
y1.APR "04-2003",
y1.MAY "05-2003",
y1.JUN "06-2003",
y1.JUL "07-2003",
y1.AUG "08-2003",
y1.SEP "09-2003",
y1.OCT "10-2003",
y1.NOV "11-2003",
y1.DEC "12-2003",
y2.JAN "01-2004",
y2.FEB "02-2004",
y2.MAR "03-2004"
from
s_d_comp_prod_qty_pvt y1
full outer
join s_d_comp_prod_qty_pvt y2
on y1.company_id = y2.company_id and y1.part_no = y2.part_no
where y1.company_id = 'C0031'
and y1.year = 2003
and y2.year = 2004
- Below I change the tables to inline views and restrict the year in the view
- and it works
select
y1.company_id,
ifsapp.customer_info_api.get_name(y1.company_id) name,
y1.part_no,
inventory_part_api.get_description('SN',y1.part_no),
y1.MAR "03-2003",
y1.APR "04-2003",
y1.MAY "05-2003",
y1.JUN "06-2003",
y1.JUL "07-2003",
y1.AUG "08-2003",
y1.SEP "09-2003",
y1.OCT "10-2003",
y1.NOV "11-2003",
y1.DEC "12-2003",
y2.JAN "01-2004",
y2.FEB "02-2004",
y2.MAR "03-2004"
from
(select * from ifsapp.s_d_comp_prod_qty_pvt where year = 2003 ) y1
full outer join
(select * from ifsapp.s_d_comp_prod_qty_pvt where year = 2004) y2
on y1.company_id = y2.company_id and y1.part_no = y2.part_no
where y1.company_id = 'C0031'
Structure of table: s_d_comp_prod_qty_pvt
YEAR VARCHAR2
COMPANY_ID VARCHAR2
PART_NO VARCHAR2
JAN NUMBER
FEB NUMBER
MAR NUMBER
APR NUMBER
MAY NUMBER
JUN NUMBER
JUL NUMBER
AUG NUMBER
SEP NUMBER
OCT NUMBER
NOV NUMBER
DEC NUMBER
Received on Tue Apr 20 2004 - 04:05:31 CDT
