Re: comparing nulls

This doesnt seem to work for all datatypes - like date for example?

ERROR at line 9:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

I tried using a number instead of 'X' like 1, and got:

ERROR at line 9:
ORA-00932: inconsistent datatypes

In article <nusf5.1048$R43.326330_at_typhoon-news1.southeast.rr.com>,   "Paul Druker" <pdruker_at_metaway.com> wrote:
> You always have to do something special for the NULL columns.
>
> In this case, you need to rewrite your query:
>
> select count(*) from t1, t2
> where
> NVL(t1.a1,'N') = NVL(t2.a2,'N')
> NVL(t1.b1,'N') = NVL(t2.b2,'N')
> NVL(t1.c1,'N') = NVL(t2.c2,'N');
>
> You may substitute N with any other character -- as long as it's the
 same in
> both NVL functions in the same line.
>
> Regards,
> Paul
>
> <orauser_at_my-deja.com> wrote in message news:8llgst$l62
 $1_at_nnrp1.deja.com...
> > I am comparing the values in two tables, like
> >
> > select count(*) from t1, t2
> > where
> > t1.a1 = t2.a2
> > t1.b1 = t2.b2
> > t1.c1 = t2.c2;
> >
> > and all is well until say t1.b1 and t2.b2 are both null. Then
> > the equality is not true.
> >
> > Can someone tell me the correct way to write this without having
> > to do something special for the nullable columns??
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jul 26 2000 - 00:00:00 CDT