Re: nvl and subquery

Thanks, Mark.
Your script works when there is an empno equal to 8888. What i want is if no empno is equal to 8888, i also hope 99 returned.

Please see the following:
SQL> select nvl(comm, 99) from emp;

NVL(COMM,99)

          99
         300
         500
          99
        1400
          99
          99
          99
          99
           0
          99
          99
          99
          99

14 rows selected.

SQL> select nvl(comm, 99) from emp where empno=8888;

no rows selected

So in this case, update will change the original comm to null. SQL> select empno, comm from emp where empno=7844;

    EMPNO COMM
--------- ---------

     7844 0

SQL> update emp set comm = (select nvl(comm, 99) from emp where empno=8888)
  2 where empno=7844;

SQL> select empno, comm from emp where empno=7844;

    EMPNO COMM
--------- ---------

     7844

This is not what i hope to be, i would like 'comm' to be 99. So could you give me more advice? Thanks.

celia

In article <3781e355.3631397_at_newshost.uk.oracle.com>,   mplant_at_uk.oracle.com (Mark Plant) wrote:
> Celia
>
> I assume what you are trying to do is set the commission for employee
> 7902 to the same value as employee 8888, unless employee 8888
> commission is null, in which case set 7902's commission to 99.
>
> The statement to do this is -
>
> update emp set comm =
> (select nvl(comm, 99)
> from emp
> where empno = 8888)
> where empno = 7902;
>
> Hope this helps.
>
> If that isn't what you are trying to do, post a description of your
> problem so we can have another go.
>
> Mark
>
> On Sun, 04 Jul 1999 02:02:31 GMT, celia9268_at_my-deja.com wrote:
>
> >Hi, everybody.
> >I would like to do an insert:
> >
> >SQL>insert into emp(comm)
> > values ( nvl(select comm from emp where empno=8888), 99)
> > where empno=7902;
> >
> >Unfortunately, this statement doesn't work. I know this can be done
in
> >pl/sql, but i really want to know whether this can be done in SQL?
> >Please help.
> >
> >Regards
> >celia
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 06 1999 - 11:42:07 CDT