All,

Thanks to everyone for their help so far.

I would like to do the following:

I have a table that has one record per user. This record stores various
information about the user and it contains a starting figure (total points
scored so far).

I have another table that stores points scored over a monthly period. On
this table I have various calculated fields that calculate the starting
score, total scores and end score for the month. It would look something
like this:

Players Table
=========
Plyer Start_score
--------------------
1 1000
2 600

Player_months
==========
Plyer Yr Mnth Strt_score Tot_score End_score
-----------------------------------------------------
1 2003 1 1000 900 1900
1 2003 2 1900 560 2460
2 2003 1 600 1000 1600
2 2003 2 1600 435 2035

I'm having problems with the Strt_score field. For this field I have
something along the lines of "take the Start_score from the Players Table
and add the sum of the previous Tot_score from the players_months. So I
would expect the first entry for player 1 to be : 1000 + 0. 1000 coming
from Players table and 0 coming from sum of no previous Player Months.

The problem I'm having is that Strt_score is showing as null and I want it
to be 0. Once I had realised the fact that number + null = null when doing a
computed field I changed the computed field to use a stored procedure. This
stored procedure is :

ALTER PROCEDURE "CALC_START_MNTH_SCORE"

(

"I_PLAYER_CODE" VARCHAR(50),

"I_PLAYER_YEAR" INTEGER,

"I_PLAYER_MONTH" INTEGER

)

RETURNS

(

"MNTH_OPEN_SCORE" FLOAT

)

AS

DECLARE VARIABLE PLAYER_OPEN_SCORE FLOAT;

DECLARE VARIABLE SUM_SCORES FLOAT;

BEGIN

SELECT PLAYERS.OPENING_SCORE FROM PLAYERS

WHERE PLAYERS.PLAYER_CODE = :I_PLAYER_CODE

INTO :PLAYER_OPEN_SCORE;

IF (PLAYER_OPEN_SCORE IS NULL) THEN

PLAYER_OPEN_SCORE = 0;

SELECT SUM(PLAYER_MONTHS.TOT_SCORE) FROM PLAYER_MONTHS

WHERE (PLAYER_MONTHS.PLAYER_CODE = :I_PLAYER_CODE) AND

(PLAYER_MONTHS.PLAYER_YEAR <= :I_PLAYER_YEAR AND

PLAYER_MONTHS.PLAYER_MONTH < :I_PLAYER_MONTH) OR

PLAYER_MONTHS.PLAYER_YEAR < :I_PLAYER_YEAR

INTO :SUM_SCORES;

IF (SUM_SCORES IS NULL) then

SUM_SCORES = 0;

MNTH_OPEN_SCORE = PLAYER_OPEN_SCORE + SUM_SCORES;

END

^

I then tested this by doing:
"SELECT MNTH_OPEN_SCORE FROM
CALC_START_MNTH_SCORE(PLAYER_MONTHS.PLAYER_CODE, PLAYER_MONTHS.PLAYER_YEAR,
PLAYER_MONTHS.PLAYER_MONTH);"
within isql, but it still returned Null. I then changed the procedure so
"MNTH_OPEN_SCORE = 0" and it STILL returned null.

What am I doing wrong?


Regards,
Marcus.