Re: How to query an empty field?

Tom Miskiewicz wrote:

> Hi!
>
> I wrote a query that uses a empty field in the where clause, but it doesn't
> deliver the expected result.
>
> SELECT count(1) FROM mytable WHERE testfield1 = 2 and testfield2 = NULL;
>
> testfield2 is a NUMBER(15);
>
>
> Thanks
> Tom
>
>

Nothing anywhere in Oracle ever equals NULL. Nor does anything in Oracle ever NOT equal NULL. Try these:

SELECT COUNT(*)
FROM all_objects
WHERE NULL = NULL; SELECT COUNT(*)
FROM all_objects
WHERE NULL <> NULL;

As Chris says ... you must use IS or IS NOT.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)