Re: nvl and subquery

A copy of this was sent to celia9268_at_my-deja.com (if that email address didn't require changing) On Tue, 06 Jul 1999 16:42:07 GMT, you wrote:

>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.
>

If thats what you want:

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

1 row updated.

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

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

      7844 99

gets it for you but:

SQL> l
  1 update emp
  2 set comm = ( select nvl(comm,99) from emp where empno = 8888 )   3* where empno = 7844 AND exists ( select null from emp where empno=8888 ) SQL> / 0 rows updated.

does the same thing EXCEPT it won't actually do the update if empno 8888 doesn't exist.

>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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 06 1999 - 12:59:38 CDT