CODEWITHSUNDEEP
sqloracle-databaseplsql
Martin
Martin

Reputation: 1090

PL/SQL function compilation errors

I'm trying to write a PL/SQL function that takes the name of a player as a parameter and returns the number of matches played in his favorite stadiums.

If the player doesn't exist in the players table, the function should returns -2. If the player exists but he doesn't have any favorite stadium, the function returns -1.

This is what I have:

create or replace function favS(pname varchar2) return number
as
    fav_stadium_count number;
begin
    select count(case when favstadiums.stadium = matches.stadium then 1 else null end) into fav_stadium_count
    from favstadiums
    right join players
    on favstadiums.player = players.name
    outer join matches
    on favstadiums.stadium = matches.stadium;
    if (count(case when favstadiums.stadium = matches.stadium then 1 else null end) > 0) then
        return fav_stadium_count;
    end if;
    if players.name is null then
        return -2;
    end if;
    if (count(case when favstadiums.stadium = matches.stadium then 1 else null end) < 1) then
        return -1;
    end if;
end;

But I get the following compilation errors:

Line 9: ORA-00933: SQL command not properly ended
Line 5: SQL Statement ignored

Any ideas on how to fix this?

Here is the relational diagram of the database if it helps:

enter image description here

Edit (for Ted):

create or replace function favS(pname varchar2) return number
as
    fav_stadium_count number;
    vplayername varchar(100);
begin
    select count(case when favstadiums.stadium = matches.stadium then 1 else null end) into fav_stadium_count,
    players.name into vplayername
    from favstadiums
    right join players
    on favstadiums.player = players.name
    left outer join matches
    on favstadiums.stadium = matches.stadium
    where name = pname;
    if (fav_stadium_count > 0) then
        return fav_stadium_count;
    end if;
    if vplayername is null then
        return -2;
    end if;
    if (fav_stadium_count < 1) then
        return -1;
    end if;
end;

Upvotes: 2

Views: 359

Answers (3)

David Aldridge
David Aldridge

Reputation: 52346

As general design advice, I'd say that this schema is missing proper primary keys -- each table should have a numeric id column as it's unwise to rely on uniqueness or immutability of real values such as names.

Then the functionality should be broken out because this code is doing too much in one place. Looking up an id for a player name probably should be in a different function, which can return null if no player is found, or an error could be raised (just as it might be if a null player name was passed to the function).

I would break out the "lookup player" functionality into another function, and if the player name doesn't exist then just return null instead of the player id.

Returning the number of favourite stadiums should then be a matter of returning an integer, 0 or greater, and no need for magic numbers to indicate other conditions.

Upvotes: 0

Dave Sexton
Dave Sexton

Reputation: 11188

I think it's your joins that have the incorrect syntax. Try: INNER JOIN and LEFT OUTER JOIN. Doing and an outer join without specifying which side has got to be wrong.

Upvotes: 0

Ted
Ted

Reputation: 4067

if (count(case when favstadiums.stadium = matches.stadium then 1 else null end) > 0) then
   return fav_stadium_count;
end if;

should just be:

if (fav_stadium_count > 0) then
   return fav_stadium_count;
end if;

As far as the following statement:

if players.name is null then
   return -2;
end if;

Is also wrong. There is no select statement there either. You should be using some sort of variable like fav_stadium_count to store the name you want.

And

if (count(case when favstadiums.stadium = matches.stadium then 1 else null end) < 1) then
    return -1;
end if;

should become:

if (fav_stadium_count < 1) then
    return -1;
end if;

Upvotes: 3

Related Questions