Message-Id: <10661.120344@fatcity.com> From: "Veera Prasad" Date: Thu, 26 Oct 2000 16:17:23 -0400 Subject: Re: Why this update is not working , can't I use nvl function in --------------926E438563F7CF13280EDFB9 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit check this out VPRASAD: TAJ>update emp set comm = nvl((select deptno from dept where emp.deptno = dept.deptno),0); 14 rows updated. The above update proves that we can have a select statement in an NVL. I am suspecting that Oracle is failing to handle NVL in a select st while loop thru. I think this is bug in Oracle , but in Sybase this works without any problems. Veera "Koivu, Lisa" wrote: > > > Veera, I don't believe you can't have a select statement in an NVL. > As long as you are in a procedure and aren't stuck to straight SQL, > execute your select into a variable and NVL that variable. > > sorry. > > Lisa Rutland Koivu > Oracle Database Administrator > Qode.com > 4850 North State Road 7 > Suite G104 > Fort Lauderdale, FL 33319 > > V: 954.484.3191, x174 > F: 954.484.2933 > C: 954.658.5849 > http://www.qode.com > > "The information contained herein does not express the opinion or > position of Qode.com and cannot be attributed to or made binding upon > Qode.com." > > -----Original Message----- > From: Veera Prasad [mailto:vprasad@olf.com] > Sent: Thursday, October 26, 2000 2:53 PM > To: Multiple recipients of list ORACLE-L > Subject: Why this update is not working , can't I use nvl function in > update st > > Hi Guys, > Can anybody tell me why this update is failing , can't I use nvl > function in update while > loop thru?, please also see the errors at the end. > > DECLARE > v_min_unique_num int; > v_max_unique_num int; > v_increment int; > begin > v_increment := 10000; > v_min_unique_num = 1; > v_max_unique_num = 100; > while (v_min_unique_num <= v_max_unique_num) loop > update > set max_capacity = nvl( (select max (col1) from > where .col1 = > where col2 = 33)), 0.0) > where .col1 between v_min_unique_num and (v_min_unique_num + > v_increment); > v_min_unique_num := v_min_unique_num + v_increment + 1; > end loop; > end; > / > > PLS-00103: Encountered the symbol "SELECT" when expecting one of the > following: > ( - + mod not null others > avg > count current exists max min prior sql stddev sum variance > execute forall time timestamp interval date > > > ORA-06550: line 78, column 31: > PLS-00103: Encountered the symbol "," when expecting one of the > following: > ; return returning and or > > Veera > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Veera Prasad > INET: vprasad@olf.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). --------------926E438563F7CF13280EDFB9 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit check this out

VPRASAD: TAJ>update emp set comm = nvl((select deptno from dept where emp.deptno = dept.deptno),0);

14 rows updated.

The above update proves that we can have a select statement in an NVL. I am suspecting that Oracle is failing
to handle NVL in a select st while loop thru. I think this is bug in Oracle , but in Sybase this works without any problems.

Veera

"Koivu, Lisa" wrote:

Veera, I don't believe you can't have a select statement in an NVL. As long as you are in a procedure and aren't stuck to straight SQL, execute your select into a variable and NVL that variable.
sorry.
Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319
V: 954.484.3191, x174
F: 954.484.2933
C: 954.658.5849
http://www.qode.com
"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."
-----Original Message-----
From: Veera Prasad [mailto:vprasad@olf.com]
Sent: Thursday, October 26, 2000 2:53 PM
To: Multiple recipients of list ORACLE-L
Subject: Why this update is not working , can't I use nvl function in
update st
Hi Guys,
Can anybody tell me why this update is failing , can't I use nvl
function in update while
loop thru?, please also see the errors at the end.
DECLARE
v_min_unique_num int;
v_max_unique_num int;
v_increment int;
begin
   v_increment := 10000;
v_min_unique_num = 1;
v_max_unique_num = 100;
while (v_min_unique_num <= v_max_unique_num) loop
update   <table1>
set max_capacity = nvl( (select max (col1) from <table2>
   where <table2>.col1 = <table1.col1 and
    <table1.col1 in (select col1 from <table3>
      where col2 = 33)), 0.0)
where <table1>.col1 between v_min_unique_num and (v_min_unique_num +
v_increment);
v_min_unique_num := v_min_unique_num + v_increment + 1;
end loop;
end;
/
PLS-00103: Encountered the symbol "SELECT" when expecting one of the
following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 78, column 31:
PLS-00103: Encountered the symbol "," when expecting one of the
following:
; return returning and or
Veera
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Veera Prasad
INET: vprasad@olf.com
Fat City Network Services    -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).