Re: Outer Join question?

>
>Lets say I have the following outer join between two tables:
>
>select d.dname, e.ename
>from dept d, emp e
>where d.deptno = e.deptno(+)
>union
>select d.dname, e.ename
>from dept d, emp e
>where d.deptno(+) = e.deptno
>
>
>Except that in the emp table deptno is a varchar2 and in the dept
>table deptno is a number type. There is of course no foreign key
>between the tables but the integrity in the relation is guaranteed the
>same as in the above example (the emp table is in fact a remote
>database in wich all the collumns have been tranformed into varchar
>types).
>
>The result I want is a list of departments without employees and a
>list of employees without departments and all the departments with
>their employees.
>
>If i try this:
>
>"where d.deptno = to_char(e.deptno) (+) "
>
>I get an ORA00933 SQL command not properly ended.
>What is the solution to this problem?
>
>TIA
[Quoted] Did you try to_number(d.deptno)=e.deptno(+) ? (The union requires the resultant [Quoted] columns to be the same data type, not the columns in the where clause)...

select d.dname, e.ename
from dept d, emp e
where to_char(d.deptno) = e.deptno(+)
union
select d.dname, e.ename
from dept d, emp e
[Quoted] where d.deptno(+) = to_number(e.deptno)

You might have to play games with leading spaces, and this might eliminate the [Quoted] use of any indexes on the converted column.

HTH
Dan Hekimian-Williams Received on Fri Oct 29 1999 - 05:07:52 CEST