Re: ROWID bug in select statement with a GROUP BY clause

AYKUT YILMAZ (AYKUTY@TRMETU.BITNET) wrote: > Below is a SELECT statement which runs quite normally under version 4.0 > but gives an error when used under version 4.1. The environment is an > NCR 3445 with SCO UNIX 3.2.2. > > The problem is caused by the 'column' ROWID in combination with 'GROUP BY', > the error issued is -294: The column 'column-name' must be in the GROUP BY > list. > > Is there a bug fix for this ROWID - GROUP BY problem and if there is, is > there an anonymous ftp site that we can get the fix? > > SELECT a.ROWID, > a.field1, > SUM (b.field2) > FROM table1 a, > table2 b > WHERE b.id = a.id > GROUP BY field1 According to the Informix ESQL/C programmers's Manual v4.00: p 3-159 Group by clause: Syntax: GROUP BY group-list Explanation: GROUP BY are required keywords group-list is a column name or a list of column names, separated by commas, that determines the group. The query result contains a single row for each set of rows that satis- fies the WHERE clause and contains a unique value or set of values in the column or columns indicated by group-list. NOTES: 1. Using a GROUP BY clause restricts what you can enter in the SELECT clause. The select-list can include aggregate functions for any column and/or the name of any column that you also list in the GROUP BY clause. The select- list can also contain an expression that involves columns in group-list. (And now the reason for the error) Do not, however, list any column in select-list that you do not also list in group-list. Since ROWID is not mentioned in the GROUP BY list, it is not allowed to place it in the select-list. Hope this helps. Marc -- -------------------------------------------------------------------------- Marc Gemis Tel: + 32 (3) 820.24.18 University of Antwerp (UIA) Fax: + 32 (3) 820.22.44 Dept. Math. and Comp. Science Telex 33.646 Universiteitsplein 1 Email: gemis@bietel.uia.ac.be B-2610 Wilrijk - Antwerpen BELGIUM A dog is for life... not just for Christmas --------------------------------------------------------------------------