At 09:45 AM 15/01/2003 +0000, you wrote:

>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.

Where are you getting those input values from? My guess is that they are
null. Inputs to stored procedures have to be constants.

By the way, contrary to other other advice, you don't use SUSPEND in a
procedure that returns only a one-row set. SUSPEND doesn't do anything,
other than perform an EXIT, under any conditions except when you are
processing a cursor (i.e. operating a FOR SELECT loop). You can (and
probably should) invoke it with EXECUTE and read the output parameters.

It would help if you would say what environment you are testing it in.

heLen