Coalesce VS ISNULL in a group by clause

[bmacbmac]

Hello,

I am trying to run this query to find all instances where booktype + '_' + bookno exist more than once. Sometimes one of the fileds will be NULL so I did this:

select * from mytable
where coalesce(booktype, '') + '_' + coalesce(bookno, '') 
in (select coalesce(booktype, '') + '_' + coalesce(bookno, '') from mytable
group by coalesce(booktype, '') + '_' + coalesce(bookno, '') 
having count(coalesce(booktype, '') + '_' + coalesce(bookno, ''))>1)

When running this query I get the following messages:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'mytable.bookType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'mytable.bookType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'mytable.bookNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'mytable.bookNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



However, If I use isnull instead of coalesce, the query will run just fine:

select * from mytable
where isnull(booktype, '') + '_' + isnull(bookno, '') 
in (select isnull(booktype, '') + '_' + isnull(bookno, '') from mytable
group by isnull(booktype, '') + '_' + isnull(bookno, '') 
having count(isnull(booktype, '') + '_' + isnull(bookno, ''))>1)

Is there a reason for this? Is coalesce picking up something that ISNULL does not pick up?

Thanks!

Brian

 

[gmmastros]

There is nothing that I am aware of that would account for this problem.

One big difference between Coalesce and IsNull is the returned data type. Coalesce will examine the data types of all of the parameters and use data type precedence to determine the returned data type. IsNull always returns the data type of the first parameter.

Here is an example:

Declare @A BigInt,
        @B Int

Select  @A = 1,
        @B = 2

Select	SQL_VARIANT_PROPERTY(Coalesce(@A, @B), 'BaseType') -- BigInt
Select	SQL_VARIANT_PROPERTY(Coalesce(@B, @A), 'BaseType') -- BigInt

Select	SQL_VARIANT_PROPERTY(IsNull(@A, @B), 'BaseType')   -- BigInt
Select	SQL_VARIANT_PROPERTY(IsNull(@B, @A), 'BaseType')   -- [!]Int[/!]

Do you get any errors if you run this?

select coalesce(booktype, '') + '_' + coalesce(bookno, '') from mytable
group by coalesce(booktype, '') + '_' + coalesce(bookno, '') 
having count(coalesce(booktype, '') + '_' + coalesce(bookno, ''))>1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros.

Nope, I didn't get an error with that script. I'm guessing Coalesce did something with the data type.

 

[SQLScholar]

How many columns are in mytable - just the two?

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------