On 12 Dec 2005 at 17:06, d_dude_2003 wrote:

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

Because aggregate query with MIN() always return one and exactly
one record, so your check for EXISTS is completely pointless.

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

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;

IF(subjectpoints IS NULL) THEN
subjectpoints = 0;

--
SY, Dimitry Sibiryakov.