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
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;
end
else
begin
subjectpoints = 0;
end

TotalPoints = TotalPoints + SubjectPoints;

UPDATE HRM_APPLICATIONS SET TOTALPOINTS = :TotalPoints
WHERE APPLICATIONID = :ApplicationID;

END
end
^

if there are no detail records in HRM_APPLICATIONSUBJECT table,
TOTALPOINTS is set to NULL value, regardless of RELEVANTEXPERIENCE
and RELEVANTQUALIFICATION field values (they are NOT NULL).

Why? How do i correct the procedure to calculate total points even
if no detail records are found?

Thanx.