Re: Something i'm missing

2007/8/15, Rumpi Gravenstein <rgravens_at_gmail.com>:
> <snip>
> select count(*) from all_users where username = nvl( '&enter_user_name',
> username);
> </snip>
>
> Ahhh, what about that three value NULL bug-a-boo...

using substitute variable for all where clause as such :) For example:
SQL> create table z (a number);

Table created.

SQL> insert into z values (null);

1 row created.

SQL> insert into z values (1);

1 row created.

SQL> select count(*) from z &q;
Enter value for q: where a is null
old 1: select count(*) from z &q
new 1: select count(*) from z where a is null

  COUNT(*)

         1

SQL> select count(*) from z &q;
Enter value for q: where a = 1
old 1: select count(*) from z &q
new 1: select count(*) from z where a = 1

  COUNT(*)

         1

SQL> select count(*) from z &q;
Enter value for q: where a = a
old 1: select count(*) from z &q
new 1: select count(*) from z where a = a

  COUNT(*)

         1

SQL> select count(*) from z &q;
Enter value for q:
old 1: select count(*) from z &q
new 1: select count(*) from z

  COUNT(*)

         2

Of course the question remains - why then we cannot substitute just all select statement :))
But it seems we cannot (SQL*Plus validate error): SQL> &q
SP2-0042: unknown command "&q" - rest of line ignored. SQL>
Se we need at least select keyword.

Gints Plivna
http://www.gplivna.eu

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