Microsoft KB Archive/70267

= PRB: Using COMPUTE with Converted Columns =

Article ID: 70267

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q70267



SYMPTOMS
A table contains an integer column. To format the output when you select data from this column, convert the column to characters and concatenate a suffix string.

However, SQL Server generates the following error if you add a COMPUTE COUNT(weight) clause to the query.

  Compute clause #1, aggregate expression #1 is not in the select list. (Msg 411, Level 16, State 2)



CAUSE
For the COMPUTE clause to function properly, the column name must appear in both the SELECT list and the COMPUTE clause. If a column in the SELECT list is being converted and/or numerically derived through a function or formula, the COMPUTE clause must contain an identical column definition. This behavior is by design.



WORKAROUND
Make the SELECT and COMPUTE clauses match.



MORE INFORMATION
A sample command and result follow.

SELECT Weight = RTRIM(CONVERT(char(10), weight)) + &quot; lbs&quot; FROM weight_table

  Weight 35 lbs 255 lbs 13 lbs 135 lbs 28 lbs

(5 rows affected)

If you modify the example as follows, you receive an error message.

SELECT Weight = RTRIM(CONVERT(char(10), weight)) + &quot; lbs&quot; FROM weight_table COMPUTE COUNT(weight)

To fix the example, modify the COMPUTE clause as follows:

SELECT Weight = RTRIM(CONVERT(char(10), weight)) + &quot; lbs&quot; FROM weight_table COMPUTE COUNT(RTRIM(CONVERT(char(10), weight)) + &quot; lbs&quot;)

  Weight 35 lbs 255 lbs 13 lbs 135 lbs 28 lbs

count ========         5

(6 rows affected)

Here is another example:

SELECT Weight = weight + 100 FROM weight_table COMPUTE AVG(weight + 100)

  Weight 35 lbs 255 lbs 13 lbs 135 lbs 28 lbs

avg ========       193

(6 rows affected)

Additional query words: Windows NT

Keywords: kbprogramming KB70267

-

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

© Microsoft Corporation. All rights reserved.