I don't believe this is quite correct. SUBSTR(), YEAR() and MONTH() are not column functions, they are scalar functions. I believe the problem may be that you cannot even use SCALAR functions in the GROUP BY unless you are in version 8 NEW FUNCTION MODE. Check to see if your site is on version 8 in COMPATIBILITY MODE?
Bill Bass United Health Care Greenville, SC > -----Original Message----- > From: IBM Mainframe Discussion List > [mailto:[EMAIL PROTECTED] On Behalf Of Lindy Mayfield > Sent: Monday, June 16, 2008 6:05 PM > To: IBM-MAIN@BAMA.UA.EDU > Subject: Re: DB2 group by with date functions > > 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 > This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ---------------------------------------------------------------------- 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
