Microsoft KB Archive/317527

= BUG: Use of COALESCE Function Against a Subquery with an Aggregate Function May Cause Error 8624 =

Article ID: 317527

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q317527



BUG #: 356377 (SHILOH_BUGS)



SYMPTOMS
If you use the COALESCE function against a subquery that has an aggregate function, you may encounter the following error message in SQL Server 2000:

Server: Msg 8624, Level 16, State 25, Line 1

Internal SQL Server error.



WORKAROUND
To work around the problem, replace the COALESCE function with the CASE function.

For example: USE pubs GO SELECT CASE WHEN((SELECT SUM(ta.au_ord) from titleauthor ta           WHERE ta.au_id = '172-32-1176' GROUP BY ta.royaltyper) IS NOT NULL) THEN (SELECT SUM(ta.au_ord) from titleauthor ta           WHERE ta.au_id = '172-32-1176' GROUP BY ta.royaltyper) ELSE 0 END GO



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
To reproduce the problem, execute the following statements against a SQL Server 2000 server: USE pubs GO SELECT COALESCE((SELECT SUM(ta.au_ord) from titleauthor ta           WHERE ta.au_id = '172-32-1176' GROUP BY ta.royaltyper), 0) GO


 * You will notice the same 8624 error even if the SUM aggregate function was replaced with another aggregate function.
 * When run against a SQL Server 7.0 server, the same statements return the expected value of 1.

Keywords: kbbug kbpending KB317527

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.