Thanks, Mike. So using "Group by year(xx)" is something new in V9 it seems.
Funny, though, I instinctively tried that syntax first and was surprised it didn't work. -----Original Message----- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bell Sent: 17. kesäkuuta 2008 0:52 To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions There isn't one - the fine manual (from V8) says grouping-expression cannot include any of the following items: | A correlated column | A host variable | A column function | Any function that is nondeterministic or that is defined to have | an external action | A scalar fullselect | A CASE expression whose searched-when-clause contains a quantified | predicate, an IN predicate using a fullselect, or an EXISTS | predicate Note column function as part of the list. The standard solution is to create a view which makes the year and month into real columns (in the view) and then you can group by the column name in the view. Mike ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html