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

} I would suggest to you that the bug was in the 4.0 release and has been } fixed in the 4.1 release, and that your statement should never have worked. } If you use a GROUP BY clause, all the non-aggregate columns should be } specified in the GROUP BY clause, either by name or by position in the } select list. } This was the conclusion that I reached; however I disagree with your next statements. } You will need to revise your select statement to read: } } SELECT a.ROWID, a.field1, SUM (b.field2) } FROM table1 a, table2 b } WHERE b.id = a.id } GROUP BY a.ROWID, a.field1; } } or: } } SELECT a.ROWID, a.field1, SUM (b.field2) } FROM table1 a, table2 b } WHERE b.id = a.id } GROUP BY 1, 2; } I tried using both of these statements using 4.0 and 4.10 - both failed with a syntax error where a.ROWID or '1' was mentioned in the GROUP BY list. } } Yours sincerely, } Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h> } -- Rory Martin Internet: dksl2vq@bell-atl.com Bell Atlantic Network Services 2221-L Broadbirch Drive Voice: 1+ (301) 989-5075 Silver Spring, MD 20904-1985 "No matter where you go, there you are." ---------------------------------------------------------------------------