You left out the suspend at the bottom of your procedure:
MNTH_OPEN_SCORE = PLAYER_OPEN_SCORE + SUM_SCORES;
SUSPEND; /* <- gotta have it!*/
END

Don't feel bad, we all do it @;-)
Brian

> -----Original Message-----
> From: news@... [mailto:news@...]On Behalf Of
> Marcus Monaghan
> Sent: Wednesday, January 15, 2003 4:46 AM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Computed fields and nulls
>
>
> 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.
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>