Xref: alice comp.databases.oracle.misc:21048
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!newsfeed.direct.ca!cyclone.bc.net!rover.ucs.ualberta.ca!sas.ab.ca!suisum
From: suisum@freenet.edmonton.ab.ca ()
Newsgroups: comp.databases.oracle.misc
Subject: Compare NULL value in WHERE clause
Date: 10 Oct 1998 15:00:31 GMT
Organization: Edmonton FreeNet, Edmonton, Alberta, Canada
Lines: 29
Message-ID: <6vnsqf$emk$1@news.sas.ab.ca>
X-Trace: news.sas.ab.ca 908031631 15060 198.161.206.7 (10 Oct 1998 15:00:31 GMT)
X-Complaints-To: news@sas.ab.ca
NNTP-Posting-Date: 10 Oct 1998 15:00:31 GMT
X-Newsreader: TIN [version 1.2 PL2.6]

If I want have the following data:

   Field1 - NOT NULL
   Field2 - NULL
   Field3 - NULL

The access key to retrieve a record is the combination of Field1, Field2,
and Field3. If I use the following statement, the query will fail becasue
of the NULL value:

  SELECT * 
    FROM my_table
   WHERE Field1 = v_field1
     AND Field2 = v_field2
     AND Field3 = v_field3;

Is there any better way to make the select statement more generic before I
use the following statement:

  SELECT *
    FROM my_table
   WHERE NVL(Field1, '/') = NVL(v_field1, '/')
     AND NVL(Field2, '/') = NVL(v_field2, '/')
     AND NVL(Field3, '/') = NVL(v_field3, '/');



--
Best regards,