PRB: Using COMPUTE with Converted Columns

ID: Q70267

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x

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)

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)

Additional query words: Windows NT

Keywords : kbprg SSrvStProc SSrvTrans SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS
Issue type :