PRB: Using COMPUTE with Converted Columns

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

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.

WORKAROUND

Make the SELECT and COMPUTE clauses match.

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)

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

   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)

   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)