Re: Why this update is not working , can't I use nvl function in

From: Veera Prasad <vprasad_at_olf.com>
Date: Thu, 26 Oct 2000 16:17:23 -0400
Message-Id: <10661.120344@fatcity.com>

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

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html>
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.

<p>Veera
<p>"Koivu, Lisa" wrote:
<blockquote TYPE=CITE>&nbsp;
<p><font size=-1>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.

<p><font size=-1>sorry.</font>
<p><font size=-1>Lisa Rutland Koivu</font>
<br><font size=-1>Oracle Database Administrator</font>
<br><font size=-1>Qode.com</font>
<br><font size=-1>4850 North State Road 7</font>
<br><font size=-1>Suite G104</font>
<br><font size=-1>Fort Lauderdale, FL&nbsp; 33319</font>
<p><font size=-1>V: 954.484.3191, x174</font>
<br><font size=-1>F: 954.484.2933</font>
<br><font size=-1>C: 954.658.5849</font>
<br><font size=-1>http://www.qode.com</font>
<p><font size=-1>"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."

<p><font size=-1>-----Original Message-----</font>
<br><font size=-1>From: Veera Prasad [<a href="mailto:vprasad_at_olf.com">mailto:vprasad_at_olf.com</a>]</font>
<br><font size=-1>Sent: Thursday, October 26, 2000 2:53 PM</font>
<br><font size=-1>To: Multiple recipients of list ORACLE-L</font>
<br><font size=-1>Subject: Why this update is not working , can't I use

nvl function in

<br><font size=-1>update st</font>
<p><font size=-1>Hi Guys,</font>
<br><font size=-1>Can anybody tell me why this update is failing , can't

I use nvl

<br><font size=-1>function in update while</font>
<br><font size=-1>loop thru?, please also see the errors at the end.</font>
<p><font size=-1>DECLARE</font>
<br><font size=-1>v_min_unique_num int;</font>
<br><font size=-1>v_max_unique_num int;</font>
<br><font size=-1>v_increment int;</font>
<br><font size=-1>begin</font>
<br><font size=-1>&nbsp;&nbsp; v_increment := 10000;</font>
<br><font size=-1>v_min_unique_num = 1;</font>
<br><font size=-1>v_max_unique_num = 100;</font>
<br><font size=-1>&nbsp;while (v_min_unique_num &lt;= v_max_unique_num)
loop</font>

update   <table1> set  max_capacity = nvl( (select  max (col1) from <table2>
    where  <table2>.col1 = <table1.col1  and

<br><font size=-1>&nbsp;&nbsp;&nbsp; &lt;table1.col1 in (select col1 from
&lt;table3></font>
<br><font size=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where col2 = 33)), 0.0)</font>
<br><font size=-1>where &lt;table1>.col1 between v_min_unique_num and (v_min_unique_num

+

<br><font size=-1>v_increment);</font>
<br><font size=-1>&nbsp; v_min_unique_num := v_min_unique_num + v_increment
+ 1;</font>
<br><font size=-1>&nbsp;end loop;</font>
<br><font size=-1>end;</font>
<br><font size=-1>/</font>

PLS-00103: Encountered the symbol "SELECT" when expecting one of the

<br><font size=-1>following:</font>
<br><font size=-1>( - + mod not null others &lt;an identifier></font>
<br><font size=-1>&lt;a double-quoted delimited-identifier> &lt;a bind

variable> avg

<br><font size=-1>count current exists max min prior sql stddev sum variance</font>
<br><font size=-1>execute forall time timestamp interval date</font>
<br><font size=-1>&lt;a string literal with character set specification></font>
<br><font size=-1>&lt;a number> &lt;a single-quoted SQL string></font>
<br><font size=-1>ORA-06550: line 78, column 31:</font>
<br><font size=-1>PLS-00103: Encountered the symbol "," when expecting

one of the

<br><font size=-1>following:</font>
<br><font size=-1>; return returning and or</font>
<p><font size=-1>Veera</font>
<p><font size=-1>--</font>
<br><font size=-1>Please see the official ORACLE-L FAQ: http://www.orafaq.com</font>
<br><font size=-1>--</font>
<br><font size=-1>Author: Veera Prasad</font>
<br><font size=-1>&nbsp; INET: vprasad_at_olf.com</font>
<p><font size=-1>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp;

FAX: (858) 538-5051
 San Diego, California        -- Public Internet access / Mailing Lists

<br><font size=-1>--------------------------------------------------------------------</font>

To REMOVE yourself from this mailing list, send an E-Mail message
 to: ListGuru_at_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).  Received on Thu Oct 26 2000 - 15:17:23 CDT