The syntax should be:

SELECT count(*) FROM foo WHERE nvl(bar, 'string') NOT LIKE 'String';

This allows any NULL values to be considered as 'String', thereby being
excluded from the result set.
Because NULL values represent nothing, you can't compare them.

Regards,
Donn Shumway
dshumway@hypercom.com
"The trouble with referees is that they just don't care
which side wins."
--Tom Canterbury




|--------+------------------------------------------->
|        |          Mike Cantrell                    |
|        |          <yomahz@devnull.org>             |
|        |          Sent by:                         |
|        |          plug-discuss-admin@lists.PLUG.pho|
|        |          enix.az.us                       |
|        |                                           |
|        |                                           |
|        |          09/13/00 08:20 PM                |
|        |          Please respond to plug-discuss   |
|        |                                           |
|--------+------------------------------------------->
  >----------------------------------------------------------------------------------------------------------|
  |                                                                                                          |
  |       To:     plug-discuss@lists.PLUG.phoenix.az.us                                                      |
  |       cc:                                                                                                |
  |       Subject:     OT: SQL question                                                                      |
  >----------------------------------------------------------------------------------------------------------|



I know it's a bit off topic but I was hoping someone here could help.


At my new job, they they don't want me accessing the database (Oracle
8i) tables directly (I guess for security purposes) so they've created
views for me to get my data from. I've ran into several strange problems
like:


SELECT count(*) FROM foo WHERE bar NOT LIKE 'String';


if the field bar is null, it doesn't count it even though it should
(right?) since null is definitely not like 'String'.  If I suspect a
possible null value in my condition (I can't even use sqlplus to tell
what fields can be null). I have to do this:

SELECT count(*) FROM foo WHERE bar NOT LIKE 'String' OR bar IS NULL;


This isn't normal for Oracle is it? Could it be a problem w/ the way the
view was created? Any thoughts? I'm going to have a meeting w/ the
Oracle guys tomorrow and I don't want to look like an idiot :)

Regards,
Mike Cantrell


________________________________________________
See http://PLUG.phoenix.az.us/navigator-mail.shtml if your mail doesn't
post to the list quickly and you use Netscape to write mail.

Plug-discuss mailing list  -  Plug-discuss@lists.PLUG.phoenix.az.us
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss