--- In firebird-support@yahoogroups.com, "d_dude_2003"
<d_dude_2003@y...> wrote:

>
> Hi there folks,
>
> I have the following SP:
>
> CREATE PROCEDURE SP_HRM_UPDATETOTALPOINTS (
> ANNOUNCEMENTID INTEGER)
> AS
> DECLARE VARIABLE APPLICATIONID INTEGER;
> DECLARE VARIABLE FLAG1 CHAR(1);
> DECLARE VARIABLE FLAG2 CHAR(1);
> DECLARE VARIABLE POINTS1 INTEGER;
> DECLARE VARIABLE POINTS2 INTEGER;
> DECLARE VARIABLE SUBJECTPOINTS INTEGER;
> DECLARE VARIABLE TOTALPOINTS INTEGER;
> begin
> SELECT CAST(CONFIG.CONFIGVALUE AS INTEGER) FROM CONFIG
> WHERE CONFIG.CONFIGNAME = 'Relevant experience'
> INTO :Points1;
>
> SELECT CAST(CONFIG.CONFIGVALUE AS INTEGER) FROM CONFIG
> WHERE CONFIG.CONFIGNAME = 'Relevant qualification'
> INTO :Points2;
>
> FOR SELECT APPLICATIONID, RELEVANTEXPERIENCE, RELEVANTQUALIFICATION
> FROM HRM_APPLICATIONS
> WHERE ANNOUNCEMENTID = :ANNOUNCEMENTID
> INTO :ApplicationID, :Flag1, :Flag2 DO
> BEGIN
> TotalPoints = 0;
> if (Flag1 = 'Y') then
> begin
> TotalPoints = TotalPoints + Points1;
> end
> if (Flag2 = 'Y') then
> begin
> TotalPoints = TotalPoints + Points2;
> end
>
> if (exists (
> SELECT SUM(HRM_GRADE.POINTS) FROM hrm_applicationsubject INNER
> JOIN HRM_GRADE
> ON hrm_applicationsubject.gradeid = HRM_GRADE.ID
> WHERE hrm_applicationsubject.applicationid = :applicationid
> )) then
> begin

I think this is your problem in the next query. If no results are
returned by the select, subject points will be left at whatever value
it happens to have before the select.

Add this next line to initialise it to 0.

SubjectPoints = 0;

> SELECT SUM(HRM_GRADE.POINTS) FROM hrm_applicationsubject INNER
> JOIN HRM_GRADE
> ON hrm_applicationsubject.gradeid = HRM_GRADE.ID
> WHERE hrm_applicationsubject.applicationid = :applicationid
> INTO :subjectpoints;

Adam