Re: casting NULLs: WAS: Oracle8i Left Join Question

From: Dmitry E. Loginov <dmitry_loginov_at_mtu.ru>
Date: Thu, 22 Nov 2001 22:46:28 +0300
Message-ID: <9tl0n2$29ut$1@gavrilo.mtu.ru>

"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

   from foo
    where not exists ( select * from bar

                        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