Multiple Rows in Singleton Select

Question

Yesterday, an anomaly occurred in our legacy software that I've never seen before. It triggers the following error: multiple rows in singleton select At procedure 'POINTS_BALANCE'

Here is the Stored Procedure

CREATE PROCEDURE POINTS_BALANCE (
    OPERATOR CHAR (3),
    PERIOD VARCHAR (75))
RETURNS (
    P_BALANCE INTEGER)
AS 

DECLARE VARIABLE B_DATE timestamp;
DECLARE VARIABLE E_DATE timestamp;
DECLARE VARIABLE ALLOWED_POINTS INTEGER;
begin
  P_BALANCE = NULL;
  SELECT DATE_BEGIN, DATE_END, TOTAL_POINTS FROM SCHED_POINT_PERIODS
    WHERE DESCRIPTION = :PERIOD INTO :B_DATE, :E_DATE, :ALLOWED_POINTS;
  IF (B_DATE IS NULL) THEN
   BEGIN    
    SELECT DATE_BEGIN, DATE_END, TOTAL_POINTS FROM SCHED_POINT_PERIODS
      WHERE cast('NOW' as timestamp) BETWEEN DATE_BEGIN AND DATE_END+1 INTO :B_DATE, :E_DATE,
      :ALLOWED_POINTS;
   END
  IF (B_DATE IS NOT NULL) THEN
    BEGIN
     E_DATE = E_DATE + 1;
     SELECT SUM(POINTS)+:ALLOWED_POINTS FROM SCHED_ACTUAL 
      WHERE OPR = :OPERATOR AND BEGIN_TIME BETWEEN :B_DATE AND :E_DATE 
      INTO :P_BALANCE;
     IF (P_BALANCE IS NULL) THEN
       P_BALANCE = ALLOWED_POINTS;
    END
  SUSPEND;
end

SCHED_ACTUAL is a table that includes the check-in, check-out times of each user SCHED_POINT_PERIODS is a table that holds the Allowed_Point values for each period (like Spring 2013, Fall 2013, Christmas Break 2013)

I'm not sure which one is a singleton. Is there a way I can tell just from this stored procedure?

Answer 1

SCHED_POINT_PERIODS are supposed to have non-overlapping periods. (i.e. they are supposed to be unique). This is the singleton that the error was referring to.

I noticed this overlap via the DB and fixed this internally. The error is resolved.