| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why this update is not working , can't I use nvl function in
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C03F77.2F038C96
Content-Type: text/plain;
charset="iso-8859-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. 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
"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;
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:
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). ------_=_NextPart_001_01C03F77.2F038C96 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printableReceived on Thu Oct 26 2000 - 13:04:27 CDT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: Why this update is not working , can't I use nvl function in =
update st </TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>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.</FONT></P>
<P><FONT SIZE=3D2>sorry.</FONT>
</P>
<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL 33319</FONT>
</P>
<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933 </FONT>
<BR><FONT SIZE=3D2>C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A></FONT>
</P>
<P><FONT SIZE=3D2>"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."</FONT></P>
<BR>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Veera Prasad [<A =
HREF=3D"mailto:vprasad_at_olf.com">mailto:vprasad_at_olf.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, October 26, 2000 2:53 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Why this update is not working , can't I =
use nvl function in</FONT>
<BR><FONT SIZE=3D2>update st </FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Hi Guys,</FONT>
<BR><FONT SIZE=3D2>Can anybody tell me why this update is failing , =
can't I use nvl</FONT>
<BR><FONT SIZE=3D2>function in update while</FONT>
<BR><FONT SIZE=3D2>loop thru?, please also see the errors at the =
end.</FONT>
</P>
<P><FONT SIZE=3D2>DECLARE</FONT>
<BR><FONT SIZE=3D2>v_min_unique_num int;</FONT>
<BR><FONT SIZE=3D2>v_max_unique_num int;</FONT>
<BR><FONT SIZE=3D2>v_increment int;</FONT>
<BR><FONT SIZE=3D2>begin</FONT>
<BR><FONT SIZE=3D2> v_increment :=3D 10000;</FONT>
<BR><FONT SIZE=3D2>v_min_unique_num =3D 1;</FONT>
<BR><FONT SIZE=3D2>v_max_unique_num =3D 100;</FONT>
<BR><FONT SIZE=3D2> while (v_min_unique_num <=3D =
v_max_unique_num) loop</FONT>
<BR><FONT SIZE=3D2>update <table1></FONT>
<BR><FONT SIZE=3D2>set max_capacity =3D nvl( (select max =
(col1) from <table2></FONT>
<BR><FONT SIZE=3D2> where <table2>.col1 =3D =
<table1.col1 and</FONT>
<BR><FONT SIZE=3D2> <table1.col1 in (select col1 =
from <table3></FONT>
<BR><FONT SIZE=3D2> where col2 =3D 33)), =
0.0)</FONT>
<BR><FONT SIZE=3D2>where <table1>.col1 between v_min_unique_num =
and (v_min_unique_num +</FONT>
<BR><FONT SIZE=3D2>v_increment);</FONT>
<BR><FONT SIZE=3D2> v_min_unique_num :=3D v_min_unique_num + =
v_increment + 1;</FONT>
<BR><FONT SIZE=3D2> end loop;</FONT>
<BR><FONT SIZE=3D2>end;</FONT>
<BR><FONT SIZE=3D2>/</FONT>
</P>
<P><FONT SIZE=3D2>PLS-00103: Encountered the symbol "SELECT" =
when expecting one of the</FONT>
<BR><FONT SIZE=3D2>following:</FONT>
<BR><FONT SIZE=3D2>( - + mod not null others <an =
identifier></FONT>
<BR><FONT SIZE=3D2><a double-quoted delimited-identifier> <a =
bind variable> avg</FONT>
<BR><FONT SIZE=3D2>count current exists max min prior sql stddev sum =
variance</FONT>
<BR><FONT SIZE=3D2>execute forall time timestamp interval date</FONT>
<BR><FONT SIZE=3D2><a string literal with character set =
specification></FONT>
<BR><FONT SIZE=3D2><a number> <a single-quoted SQL =
string></FONT>
<BR><FONT SIZE=3D2>ORA-06550: line 78, column 31:</FONT>
<BR><FONT SIZE=3D2>PLS-00103: Encountered the symbol "," when =
expecting one of the</FONT>
<BR><FONT SIZE=3D2>following:</FONT>
<BR><FONT SIZE=3D2>; return returning and or</FONT>
</P>
<P><FONT SIZE=3D2>Veera</FONT>
</P>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Veera Prasad</FONT>
<BR><FONT SIZE=3D2> INET: vprasad_at_olf.com</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------= -----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
 |
 |