oracle怎实现全连接？急！！

[xieguogui]

a.f = b.f(+) 或 a.f(+) = b.f 都可以，但我想左右都连接上，可不可以
a.f(+)=b.f(+)，好像不行，怎么办？请教！

[xieguogui]

select * from
(

(
select kh.sm,kh.cnt,xh.cnt
from
(select sm,count(*) cnt from tbm_yd,tyt_yhda where dm=yd and to_char(khsj,'yyyymmdd') = '20030825' group by sm) kh,
(select sm,count(*) cnt from tbm_yd,tyt_xhyd where dm=yd and to_char(xhsj,'yyyymmdd') = '20030825' group by sm) xh
where kh.sm=xh.sm(+)
)
union all
(
select kh.sm,kh.cnt,xh.cnt
from
(select sm,count(*) cnt from tbm_yd,tyt_yhda where dm=yd and to_char(khsj,'yyyymmdd') = '20030825' group by sm) kh,
(select sm,count(*) cnt from tbm_yd,tyt_xhyd where dm=yd and to_char(xhsj,'yyyymmdd') = '20030825' group by sm) xh
where kh.sm(+)=xh.sm
)

)

ORA-00918: column ambiguously defined
为什么？

[xieguogui]

除了用union还有其他办法吗？弱oracle!!

[enhydraboy]

select ...
from a,f
where a.f = b.f(+)
union
select ...
from a,f
where a.f(+) = b.f

[jiezhi]

SELECT c.customer_id, c.o.order_id, c.account_mgr_id, o.sales_rep_id
FROM customers c FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

[xieguogui]

full outer join 好像是SQL Server才可以用的吧？

[jiezhi]

a full out join b on ... --->
Return all rows from the a table and all rows from the b table.
Rows that do not satisfy the ON conditionare extended with nulls.

[jiezhi]

in oracle9i,you can use full out join

[yaotiebing]

使用 || 连接符

[enhydraboy]

也许oracle 9i支持这种写法吧。

[yaotiebing]

SQL> SELECT ename||job AS "Employees"
2 FROM emp;

[xieguogui]

我用的是oracle 8i怎么办？难道就没有办法了吗？oracle就这么弱吗？

[enhydraboy]

左连接 union 右连接，不就可以了。

[LGQDUCKY]

select A.*,B,* from a,B where a.f = b.f(+)
union ALL
select A.*,B,* from a,B where a.f(+) = b.f

[LGQDUCKY]

用enhydraboy(乱舞的浮尘)：的方法无觉得好，
select A.*,B.* from a,B where a.f = b.f(+)
union ALL
select A.*,B.* from a,B where a.f(+) = b.f;

[xieguogui]

真的没有人会吗？

[enhydraboy]

"ORA-00918: column ambiguously defined为什么？"
仔细检查你里面的连接部分的select语句，肯定没有在同名的列前，加上表名。

"除了用union还有其他办法吗？弱oracle!!",方法有，只不过你没有耐性去解决你的语句bug。