When run under SQL Anywhere 12.0.1.3298, the following code raised the following exception: SQLCODE = 109, SQLSTATE = 01003, ERRORMSG() = Null value eliminated in aggregate function.

This appears to be an isolated incident.

There are two problems with this:

First, SQLCODE 109 isn't an error, it's a warning, and it shouldn't raise an exception.

Second, none of the code that could have raised the exception contains a query, let alone an aggregate function.

Here's the full diagnostic message:

2012-07-07 10:15:45.084 Full Build ----- 1000001799 211a4g1(211eh2) SQLCODE = 109, SQLSTATE = 01003, 
   ERRORMSG() = Null value eliminated in aggregate function [---]

The substring "211a4g1" indicates the exception was raised somewhere between these two assignment statements:

SET @diagnostic_location = '211a4g1';
...
SET @diagnostic_location = '211a4g2';

The substring "(211eh2)" means the EXCEPTION handler is the one at the bottom of the following code. These substrings are unique across all the code in the database.

(PLEASE IGNORE the fact that indenting has been ignored for random lines.)

CREATE PROCEDURE rroad_monitor_alerts (
...
         BEGIN -- ignore all exceptions

-- Note: The datediff_msec_between_target_and_local value is used to adjust the target rroad_group_2_property_pivot.TransactionStartTime to Foxhound time.

SELECT MAX ( DATEDIFF ( SECOND, 
                                    IF rroad_group_2_property_pivot.TransactionStartTime IN ( '', '1900-01-01', '0000-01-00 00:00:00.000000' )
                                       THEN @sample_recorded_at
                                       ELSE rroad_f_dateadd_msec ( rroad_sample_set.datediff_msec_between_target_and_local,  
                                                                   rroad_group_2_property_pivot.TransactionStartTime )
                                    ENDIF, 
                                    @sample_recorded_at ) ) 
                      * 1000 
              INTO @alert28_max_transaction_time
              FROM rroad_group_2_property_pivot
                      INNER JOIN rroad_sample_set
                              ON rroad_sample_set.sample_set_number = rroad_group_2_property_pivot.sample_set_number
             WHERE rroad_sample_set.sampling_id                   = @sampling_id
               AND rroad_sample_set.sample_set_number             = @sample_set_number 
               AND rroad_group_2_property_pivot.sampling_id       = @sampling_id
               AND rroad_group_2_property_pivot.sample_set_number = @sample_set_number 
               AND rroad_group_2_property_pivot.TransactionStartTime NOT IN ( '', '1900-01-01', '0000-01-00 00:00:00.000000' );

EXCEPTION WHEN OTHERS THEN

SET @alert28_max_transaction_time = 0; -- ignore "Cannot convert '...' to a timestamp"

END; -- ignore all exceptions

----------------------------------
         -- Alert #28 increment / decrement

SET @diagnostic_location = '211a4g1';

IF @alert28_max_transaction_time >= @alert28_threshold_max_transaction_time THEN

-- Increment the counter by 1.

SET @alert28_actual_max_transaction_time_threshold_reached_sample_counter = @alert28_actual_max_transaction_time_threshold_reached_sample_counter + 1;

ELSE

IF @alert28_actual_max_transaction_time_threshold_reached_sample_counter > @alert28_threshold_max_transaction_time_sample_count THEN

-- Pull the counter back down to the threshold so decrementing to zero can begin.

SET @alert28_actual_max_transaction_time_threshold_reached_sample_counter = @alert28_threshold_max_transaction_time_sample_count;

ELSE

-- Decrement the counter by 2, so it sinks to zero faster than it rose to the threshold.

SET @alert28_actual_max_transaction_time_threshold_reached_sample_counter = GREATER ( 0, @alert28_actual_max_transaction_time_threshold_reached_sample_counter - 2 );

END IF;

END IF;

----------------------------------
         -- Alert #29 increment / decrement

SET @diagnostic_location = '211a4g2';
...
EXCEPTION

WHEN OTHERS THEN

SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

CASE

WHEN ( @sqlcode = 0 AND @sqlstate = '00000' ) THEN
...
         WHEN @suspect_sample_set_number IS NOT NULL
          AND @sqlcode IN ( -1280,        -- Underflow when converting '%1'
                            -638,         -- Right truncation of string data    
                            -628,         -- Division by zero
                            -158,         -- Value %1 out of range for destination
                            -157 ) THEN   -- Cannot convert %1 to a %2
...
         ELSE

-----------------------------------------------------------------------
            -- Report and continue.

CALL rroad_exception ( STRING ( 
               @diagnostic_location, '(211eh2)', 
               ' SQLCODE = ', @sqlcode,  
               ', SQLSTATE = ', @sqlstate,  
               ', ERRORMSG() = ', @errormsg ) );

END CASE;
...
2012-07-07 10:15:45.084 Full Build ----- 1000001799 211a4g1(211eh2) SQLCODE = 109, SQLSTATE = 01003, 
   ERRORMSG() = Null value eliminated in aggregate function [---]

asked 07 Jul '12, 12:21

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 07 Jul '12, 12:25

Maybe @alert28_actual_max_transaction_time_threshold_reached_sample_counter is NULL when reaching the Greater(...) function?

(09 Jul '12, 05:15) Martin
Replies hidden
Comment Text Removed

SET is not a query, and GREATER() is not an aggregate function.

(09 Jul '12, 05:28) Breck Carter

Hmm, I don't think GREATER would raise a warning - it's not an AGGREGATE function, and GREATER(1, NULL) returns NULL as expected...

(09 Jul '12, 05:39) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 07 Jul '12, 12:21

question was seen: 2,345 times

last updated: 09 Jul '12, 05:39