Microsoft KB Archive/180300

= BUG: Aggregate Functions in a CASE Expression in a Correlated Subquery May Cause an Access Violation =

Article ID: 180300

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q180300



SYMPTOMS
A query that contains two or more aggregate functions within a CASE expression contained within a correlated subquery may cause a thread level access violation (AV).

Any combination of the MIN, MAX, or SUM functions could cause the problem in this scenario. The COUNT, COUNT(*), and AVG functions do not exhibit this behavior.

Note that a query that meets the problem criteria outlined above may not cause the AV if there is less data in the queried tables.



CAUSE
This problem can occur if the correlated subquery is in the SELECT list or in the WHERE clause.



WORKAROUND
To work around this problem, break the query into two separate SQL statements. The first query should collect the required information into a temporary table; the second can then produce the final result set.

For example, suppose you had the following query, which causes this problem: SELECT anl.id, anl.type, production = (SELECT        CASE            WHEN SUM(production) >99999 THEN 1            WHEN SUM(production) <=99999  AND SUM(production) > 500 THEN 2            ELSE 3         END         FROM    anl_ar         WHERE anl_ar.id = anl.id         AND    anl_ar.type = anl.type      ) FROM   lan, anl, tak WHERE anl.lan = lan.lan AND   tak.tak_id = anl.tak_id To avoid the problem, you can rewrite the above query as: SELECT anl.id, anl.type, production_sum = (SELECT SUM(production)        FROM    anl_ar         WHERE anl_ar.id = anl.id         AND    anl_ar.type = anl.type      ) INTO   #temp1 FROM  lan, anl, tak WHERE  anl.lan = lan.lan AND  tak.tak_id = anl.tak_id

SELECT id, type, production = CASE WHEN production_sum >99999 THEN 1 WHEN production_sum <=99999 AND production_sum > 500 THEN 2 ELSE 3 END FROM #temp1



STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords: kbbug KB180300

-

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

© Microsoft Corporation. All rights reserved.