Post reply

select count(distinct col) with nulls

  • February 1, 2007 at 1:05 pm

    #75124

    I have a problem with the application that issues a select distinct count of a column that has NULLS value on sql server 2000. The application doesn't handle the warning message returned and shuts down. I cannot change the application code… is there a way to suppress the warning messages so ths application will not stop?

    Below is the exact statement that causes the problem, but I cannot reproduce it in QA. I guess it does handle the nulls for me.

    create table testing_bug (id bigint, col1 varchar(50))

    insert into testing_bug values(1, 'ttt');

    insert into testing_bug values(19, null);

    insert into testing_bug values(12, 'nnn');

    insert into testing_bug values(15, 'mmm');

    insert into testing_bug values(14, 'yyy');

    select count(distinct col1) from testing_bug

    When run form QA I get this warning:

    1 row(s) affected)

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Is there a way to supress the warning messages in the SQL server on a server or database level?

    Thanks a lot, mj

  • February 1, 2007 at 1:38 pm

    #687215

    Hello,

    Use

    SET ANSI_WARNINGS  OFF

    to suppress the warnings.

    Hope this helps.

    Thanks

     


    Lucky

  • February 1, 2007 at 1:53 pm

    #687221

    Yep, I tried that but start getting questions on what is the downside for the whole server, etc.

    Is there another way to fix that?

    Thanks, mj

  • February 2, 2007 at 6:02 am

    #687386

    other than setting ansi warnings off

    I believe the only way is to modify your application code

    select count(distinct col1) from testing_bug where col1 is not null

     

    or in 2005 using TRY CATCH

  • February 2, 2007 at 6:41 am

    #687396

    I'd try the following:

    select count(isnull(col1,"Null",col1) from testing_bug goup by col1

    That should avoid the error all together and include the count for records with Null.

    Regards,
    Matt

  • February 2, 2007 at 10:42 am

    #687489

    Do you have access to the source of the application? Can you make any modifications to the system?

    Live to Throw
    Throw to Live
    Will Summers

  • February 2, 2007 at 12:36 pm

    #687530

    Only to the SQL server. Cannot change the query either. The application dev team will send me a hot fix, but I do not know when that will happen...

    Thanks a lot, mj

  • February 4, 2007 at 5:23 pm

    #687658

    Try this:

    select COUNT(distinct ISNULL(col1,'')) from testing_bug

  • February 6, 2007 at 2:56 am

    #687954

    I thought that SET ANSI_WARNINGS was a session thing and could be set at the beginning and end of the query?

    Ade


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

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