PRB: Using COMPUTE with Converted Columns (70267)

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.

MORE INFORMATION

A sample command and result follow.

   SELECT Weight = RTRIM(CONVERT(char(10), weight)) + " lbs"
   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)) + " lbs"
   FROM weight_table
   COMPUTE COUNT(weight)

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

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

   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)

PRB: Using COMPUTE with Converted Columns (70267)

SYMPTOMS

CAUSE

WORKAROUND

MORE INFORMATION