Help, procedure not compiling [message #18366] |
Tue, 29 January 2002 10:53 |
Hitesh
Messages: 12 Registered: October 2000
|
Junior Member |
|
|
I am trying to perform a simple select query where the value of variable "x" is used as a denominator in a mathematical expression. See the second select query for reference. I received an error shown at the end of the procedure. I would appreciate any feedback. thank you.
Declare
x number;
Begin
select count(*) into x
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG';
select creator, count(creator)/x
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG'
group by creator
order by count(creator) desc
END;
/
ERROR at line 16:
ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
, ; for
The symbol ";" was substituted for "END" to continue.
|
|
|
|
Re: Help, procedure not compiling [message #18370 is a reply to message #18366] |
Tue, 29 January 2002 12:37 |
Hitesh
Messages: 12 Registered: October 2000
|
Junior Member |
|
|
Hi Jim,
Thanks to your help, I'm a little closer to getting this procedure to work. However, being somewhat of an ORACLE novice, I'm not sure i understand your second suggestion about an INTO CLAUSE. Will I need to create a dummy table and then reference that table in my procedure's sql statement? If you have an example, that might be of some help. Thanks again for the help!
|
|
|
Re: Help, procedure not compiling [message #18379 is a reply to message #18366] |
Tue, 29 January 2002 15:49 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
set serveroutput on
Declare
x number;
l_creator GREATBRITAIN.creator%type;
l_count number;
Begin
select count(*) into x
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG';
select creator, count(creator)/x INTO l_creator,l_count
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG'
group by creator
order by count(creator) desc;
EXCEPTION
WHEN too_many_rows then
dbms_output.put_line('query returned more than one row.. use cursor');
END;
|
|
|