nvl and subquery

[celi...@my-deja.com]

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.

[이경록]

<celi...@my-deja.com>이(가) 아래 메시지를
news:7lmfbk$i3p$1...@nnrp1.deja.com에 게시하였습니다.

This code has some error. First of all number of columns does'nt match.
You insert data into comm column(in emp table) <== only one column
but you stated two columns separated by ,(comma).

insert into emp(comm)
select nvl(comm,99) from emp where empno=8888;
<== change the statement like this and where statement does not work in
insert statement.
(there are two where statement ah? I think you would update a table ...)

[Kenneth C Stahl]

INSERT INTO EMP (COMM)
VALUES (SELECT NVL(COMM,99)
FROM EMP
WHERE EMPNO=8888);
WHERE EMPNO=7902;

Your NVL() was in the wrong place.

Ken

[Mark Plant]

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

[celi...@my-deja.com]

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...@newshost.uk.oracle.com>,

[Thomas Kyte]

A copy of this was sent to celi...@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.

--
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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation