Post reply

Null value is eliminated by an aggregate or other SET operation

  • August 18, 2014 at 1:12 am

    #148671

    I am having simple SPROC where aggregae function is being used.

    I am getting below warning while executing SPROC in SSIS.

    Null value is eliminated by an aggregate or other SET operation

    I added SET ANSI_WARNINGS OFF to script.

    Also isNULL is added for NULLS.

    But error continues.

    I cant access SSIS package as it is on remote machines but many of my earlier SPROCS are working there.

    --------- Below is Code ---------

    CREATE PROCEDURE [dbo].[CPCALC_FixedFeeEligible]

    @ProjectID UNIQUEIDENTIFIER,

    @PARA_VAR NUMERIC (18,2) output

    AS

    SET ANSI_WARNINGS OFF

    begin

    Declare @EngagementID UNIQUEIDENTIFIER

    Declare @ffEligible NUMERIC (18,2)

    select @EngagementID = engagementid from dbo.project where projectid = @ProjectID

    IF ( (select count(*) from dbo.fixedfeeschedule ff where ff.Deleted =0 and ff.prepaid<>1 and ff.EngagementID = @EngagementID ) > 0)

    Begin

    select @ffEligible = sum(CASE WHEN NOT(ff.Prepaid = 1) THEN ISNULL(ff.invoicedamount,0) ELSE 0 END) FROM dbo.fixedfeeschedule ff where ff.Deleted =0 and ff.EngagementID = @EngagementID

    end

    ELSE

    Begin

    set @ffEligible = 0

    END

    set @PARA_VAR = isNull(@ffEligible,0)

    END

  • August 18, 2014 at 10:33 am

    #1738951

    This behavior is part of the standard and should not be affecting the results of your query. I recommend you read these 2 articles:

    http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/[/url] - which is old, but AFAIK NULL handling by aggregates hasn't changed.

    http://msdn.microsoft.com/en-us/library/ms187810.aspx - where the top line states that "Null values are ignored."

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply