Re: Something i'm missing

<snip>
select count(*) from all_users where username = nvl( '&enter_user_name', username);
</snip>

Ahhh, what about that three value NULL bug-a-boo...

SQL> CREATE TABLE test( col varchar2(1) )   2 /

Table created.

SQL> INSERT INTO test VALUES ( null )
  2 /

1 row created.

SQL> INSERT INTO test VALUES ( 'Y' )
  2 /

1 row created.

SQL> COMMIT
  2 /

Commit complete.

SQL> SELECT COUNT(*)
  2 FROM test
  3 /

  COUNT(*)

         2

SQL> SELECT COUNT(*)
  2 FROM test
  3 WHERE col = NVL(NULL, col)
  4 /

  COUNT(*)

         1

SQL> It strikes me that the more robust answer must make allowances for the NULL match like:

SELECT count(*)
  FROM test
WHERE ( '&A' IS NULL OR col = '&A')

which results in:

SQL> SELECT COUNT(*)
  2 FROM test
  3 WHERE ( '' IS NULL OR col = '' );

  COUNT(*)

         2

SQL> SELECT COUNT(*)
  2 FROM test
  3 WHERE ('Y' IS NULL OR col = 'Y' );

  COUNT(*)

         1

Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l