prb with function returning a value

[hedub]

Hi,
my table "deltakelse" looks like this:
CREATE TABLE DELTAKELSE (
gulekort Smallint,
rodekort Smallint,
scoringer Smallint,
kampid Smallint NOT NULL,
spillerid Smallint NOT NULL,
CONSTRAINT PN_DELTAKELSE PRIMARY KEY (
kampid,
spillerid
)
where I want to make a function that returns the sum value of all inserts that are made to the attribute "scoringer" and then list (with a select quiry after) top 5 scores(scoringer).

This is my solution.. and the function is created but with compilation error:

create or replace function SisteTopScore is
cursor cGetScore is
select distinct scoringer from (

select spillerid, sum(scoringer) scoringer
from deltakelse where spillerid=spillerid
group by spillerid;
)
order by scoringer desc;

l_score number;
l_cnt number :=0;

begin
open cGetScore;
loop
fetch cGetScore into l_score;
l_cnt:=l_cnt+1;
exit when cGetScore%notfound or l_cnt=5;
end loop;
close cGetScore;

if l_cnt<5 then
l_score:=1;
end if;

return (l_score);
end;
/
And when I run my select it returns this error:ORA-06575: Package or function SISTETOPSCORE is in an invalid state.

probarly because of the function with compilation error. How can I make this work? pls advice.

 

[BJCooperIT]

The syntax of your function should include a &quot;return&quot; clause. &quot;Return&quot; specifies the datatype of the value returned by the function. In this case l_score is number so you would add &quot;return number&quot;. That may be the source of your compilation error which marks your function invalid in the database. The ORA-06575 is received when you try to execute the invalid function.

If you are getting a different compilation error then it would be helpful to post that as well.

Good luck!
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[sem]

I see at least 2 errors: missing RETURN clause in header and extra semicolon after &quot;group by spillerid&quot; in subquery. Regards, Dima

 

[BJCooperIT]

You are right Dima. I often miss punctuation myself due to the fact that I am using a monitor with only 16 colors. Tek-tips posts do not display well on a patterned background (hint, hint!). I need to remember to block the posts with the mouse.

Hedub,
I have re-examined the code and find that you said you want &quot;a function that returns the sum value all inserts that are made to the attribute scoringer&quot;. What you are returning is the last l_score fetched. If you want all rows &quot;that are made to the attribute &quot;scoringer&quot; and then list (with a select quiry after) top 5 scores(scoringer)&quot; why is the function only reading the first 5 rows?

It seems to me that you might want to do:
select sum(scoringer) scoringer
into v_scoringer
from deltakelse
where spillerid=spillerid <-- This does not do anything
group by spillerid;

This gets your sum value you wanted to return by returning v_scoringer. Your &quot;where&quot; clause does not do anything. Perhaps you meant to only sum for a particular spillerid? In which case you would have an input parameter for the spillerid and your select would be:

select sum(scoringer) scoringer
into v_scoringer
from deltakelse
where spillerid = p_spillerid;

As for the top 5 values, a function only returns a single value. Doesn't your top 5 query belong beneath the code that calls your function?
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[hedub]

The scenario is like this: A fotballplayer scores goals. The function is supposing to return the 5 players that have most goals, a top 5 scorelist. Spillerid is the PK of the table SPILLER (in english: player) and are related to DELTAKELSE. DELTAKELSE (in english :to join, attending etc) is a table with attribs for storing info about the players scores, yellow and red cards. As you can see the spillerid is also a foreign as well as part of a multi primarykey in deltakelse. I have done this because deltakelse is an assosiative entity between SPILLER (player) and KAMP (game). So, when storing info about a game you might also want to give credit to what player that are scoring goals. And that is done by the top5 score list. The 5 players that are scoring most goals. Hope you understand my poor english, hehe Thanks for all comments on this.

 

[BJCooperIT]

A function returns a single value. It seems to me that you would do better using REF CURSOR to return you the top 5 rows. The only way the function could do this is to return concatenated values which you would have to substring apart after you call the function.
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000