Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: casting NULLs: WAS: Oracle8i Left Join Question
"Neil Zanella" <nzanella_at_cs.mun.ca> wrote in message
news:b68d2f19.0111211616.53cd8a48_at_posting.google.com...
> Nicholas Carey <ncarey_at_speakeasy.org> wrote in message
news:<Xns915FB970E310Fncareyspeakeasyorg_at_207.126.101.92>...
>
> > select foo.id foo_id ,
> > bar.id bar_id
> > from foo
> > join bar on bar.id = foo.id
> > UNION
> > select foo.id foo_id ,
> > to_number(NULL) bar_id
> > from foo
> > where not exists ( select * from bar
> > where bar.id = foo.id
> > )
>
> Thank you for pointing out that Oracle has a to_number() function.
> This solves the problem but the resulting code is not portable in
> the sense that it will produce errors with database systems other
> than Oracle.
Try to use implicit casting:
select foo.id foo_id ,
bar.id bar_id
from foo
join bar on bar.id = foo.id
UNION
select foo.id foo_id , 0+NULL bar_id
where bar.id = foo.id )
> Other DBMS perform the cast automatically. After all
> what else could the user have in mind when placing a NULL in some
> column field? I strongly believe that is should be allowed.
>
> Thank you for pointing out the bugfix,
>
> Neil
Received on Thu Nov 22 2001 - 13:46:28 CST
![]() |
![]() |