PLS-00103 [message #284124] |
Wed, 28 November 2007 23:53 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Hi All .. Where am I doing wrong?
1 create or replace procedure uza
2 as
3 cursor c1 is select regno,rpad(grn.name,40) as name,grn.faculty,round(cgpa,2) as cgpa,
4 (dense_rank () over(partition by substr(regno,1,4) order by round(cgpa,2) desc)) as cgpa_rank,
5 (count(regno) over(partition by substr(regno,1,4))) as batch_total
6 from it,grn
7 where it.regno=grn.rn
8 and sc=1 and sy=2004
9 and regno not like '9%';
10 begin
11 for x in c1 loop
12 null;
13 end loop;
14* end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE UZA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/16 PLS-00103: Encountered the symbol "OVER" when expecting one of
the following:
. ( ) , * % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
SQL>
Thanks
rz
|
|
|
|
Re: PLS-00103 [message #284133 is a reply to message #284124] |
Thu, 29 November 2007 00:08 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Yes , of course. below is the output
SQL> select regno,rpad(grn.name,40) as name,grn.faculty,round(cgpa,2) as cgpa,
2 dense_rank () over(partition by substr(regno,1,4) order by round(cgpa,2) desc) as cgpa_rank,
3 count(regno) over(partition by substr(regno,1,4)) as batch_total
4 from it,grn
5 where it.regno=grn.rn
6 and sc=1 and sy=2004
7 and regno not like '9%'
8
SQL> /
MOD-Edit: Frank removed results on poster's request, since it showed personal data
rz
[Updated on: Mon, 28 June 2010 01:25] by Moderator Report message to a moderator
|
|
|
Re: PLS-00103 [message #284140 is a reply to message #284124] |
Thu, 29 November 2007 00:27 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Analytic functions (and CASE expressions) are not supported in 8i PL/SQL where as they are supported in SQL.
Create a view with the same query and use the view in the procedure.
Regards
Michel
[Updated on: Thu, 29 November 2007 00:27] Report message to a moderator
|
|
|