A Google search turn this up: SQL0122 - Column &1 or function specified in SELECT list not valid . When using a GROUP BY clause, all of the columns in the SELECT list must be in the GROUP BY clause or be contained within a column function (note that this is different than being used in a scalar function) as explained in the SQL Reference as follows:
If GROUP BY or HAVING is used: o Each column name in the select list must either identify a grouping column or be specified within a column function. o The RRN, PARTITION, NODENAME, and NODENUMBER functions cannot be specified in the select list. o The select list is applied to each group of R, and the result contains as many rows as there are groups in R. When the select list is applied to a group of R, that group is the source of the arguments of the column functions in the select list. Example: "Select workdept, salary from employee GROUP BY workdept" would fail because salary is not in the GROUP BY. "Select field1,DATE(field2) from T1 GROUP BY field1" fails since field2 is not in a column function or the GROUP BY. "Select workdept, avg(salary) from employee GROUP BY workdept order by 2" would work fine because workdept is a grouping column and salary is specified in a column function (AVG). Remember, Google is your friend! Joe Kelly On 3/15/06, Ron Mast <[EMAIL PROTECTED]> wrote: > Hi All, > I have a query and I'm grouping by 2 fields and the query dumps no > problem with those 2 fields in the SELECT statement, but when I try and > add a field to the SELECT statement I get the following error: > > Error Diagnostic Information > ODBC Error Code = S1000 (General error) > > > [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0122 - > Column CPARTW or expression in SELECT list not valid. > > > Can anyone point me in the right direction? Any help is greatly > appreciated. > > Thanks, > > Ron > > > > _______________________ > This e-mail and any files transmitted with it are confidential and are > intended solely for the use of the individual to whom they are addressed. If > you are not the intended recipient or the individual responsible for > delivering the e-mail to the intended recipient, please be advised that you > have received this e-mail in error and that any use, dissemination, > forwarding, printing, or copying of this e-mail is strictly prohibited. > > > _______________________________________________ > Reply to DFWCFUG: > [email protected] > Subscribe/Unsubscribe: > http://lists1.safesecureweb.com/mailman/listinfo/list > List Archives: > http://www.mail-archive.com/list%40list.dfwcfug.org/ > http://www.mail-archive.com/list%40dfwcfug.org/ > DFWCFUG Sponsors: > www.HostMySite.com > www.teksystems.com/ > _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
