-
About Full Outer Join
I have written a full outer join as follows:
select * from
(select resc_code, consume_qty from resc_consume where tran_type = 'TR' and req_dest = 'IT' and
year_month = '200302') b full outer join
(select resc_code, std_um, sum(bill_menu_qty) bqty, sum(bill_spml_qty)
sqty, sum(bill_supp_qty) supp_qty
from sum_resc_uplift
where kitchen = 'TU'
and year_month = '200302'
group by resc_code, std_um) a
using (resc_code);
But this query doesnt return what I expect. It just work like a left outer join only, i.e., return all the records in subquery of b. But if i run the following sql, it works well:
select * from (select resc_code from resc_consume) a full outer join
(select resc_code from sum_resc_uplift) b using resc_code);
Is that I cannot use subquery in full outer join ?
_____________________________
Loren
-
I do not know there is a bug involved in "FULL OUTER JOIN" in oracle.
My test also proved a wrong result.
12:21:48 DEVW3>select * from t1;
ID NAME
---------- ------------------------------
1 CAR
2 CYCLE
3 PLANE
12:55:45 DEVW3>select * from t2 ;
ID SEQ SQTY
---------- ---------- ----------
1 1 10
1 2 20
2 1 30
2 2 40
4 1 10
select * from ( select id from t1 ) A full outer join
( select id , sum(sqty) from t2 group by id) B using (id)
2:58:00 DEVW3>/
ID SUM(SQTY)
--------- ----------
1 30
2 70
3
4
I expected a value of 10 for the ID, 4 but the query did not bring.
Let me do it in old method.
select t2.id , sum(sqty) from t1 , t2
where t1.id(+) = t2.id
group by t2.id
union
select t1.id , sum(sqty) from t1 , t2
where t1.id = t2.id(+)
group by t1.id
2:59:52 DEVW3>/
ID SUM(SQTY)
--------- ----------
1 30
2 70
3
4 10
Here it is what I wanted.
Finally the UNION gave me correct result.
Is it a BUG or NO BUG, I do not know. Contact Oracle Support to get more info.
Has any one experienced this type of query? Please share your views.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|