Re: Outer Join question?

You can put the plus sign inside of the function, then Oracle parses just fine:

 ... where d.deptno = to_char(e.deptno(+) )

Robert Proffitt

In article <19991028230752.19742.00002082_at_ng-fb1.aol.com>,   danhw_at_aol.com (DanHW) wrote:
> >
> >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
>
> Did you try to_number(d.deptno)=e.deptno(+) ? (The union requires the
resultant
> 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
> where d.deptno(+) = to_number(e.deptno)
>
> You might have to play games with leading spaces, and this might
eliminate the
> use of any indexes on the converted column.
>
> HTH
> Dan Hekimian-Williams
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 29 1999 - 17:53:30 CEST