I don't believe this solves the original question. The issue isn't with the meta data, but the lack of a built in function to allow for different groupings.
I think that this might solve the problem.... Select a,b,c... YEAR(x)foo, MONTH(x) bar, DAY(x) retz Then do a GROUP BY foo or bar or retz. This should work... in theory at least, assuming that there is a YEAR(), MONTH() and DAY() functions available. (If not, you can always write them... ;-) -Mike > -----Original Message----- > From: Alex Miller [mailto:[EMAIL PROTECTED] > Sent: Sunday, March 05, 2006 12:51 PM > To: Max Ten; Stanley Bradbury; Derby Discussion > Subject: RE: Derby Function > > Hi Max, > > Another option would be to use the MetaMatrix Query product on top of > Derby. MetaMatrix Query is a federated SQL engine with a JDBC driver > that supports Oracle, SQL Server, Sybase, DB2, MySQL, PostgreSQL, Derby, > and others. Our tool allows you to import metadata from one or more of > these databases and use SQL to access one or many of them in a single > SQL statement. > > You can see our new developer web site at > http://devcentral.metamatrix.com and download a free trial of MetaMatrix > Query at http://devcentral.metamatrix.com/download/Home. We currently > support Derby only in network (client/server) mode as there are some > issues with classloaders and the single engine per VM constraints of > Derby. > > With respect to your particular need, MetaMatrix supports functions in > GROUP BY and can be used to enhance Derby with this functionality. > Unfortunately, there is an issue in our Derby connector that is > preventing this from working in MetaMatrix Query 5 EA1, which is the > current download. We will have EA2 out in a few days and that issue is > resolved. > > For more details on an example query just like yours (and how it works > in EA2), see my blog on the subject at > http://devcentral.metamatrix.com/blog/alex/2006/02/28/Enhancing-Apache-D > erby-with-MetaMatrix. > > Alex Miller > Chief Architect > MetaMatrix > > > -----Original Message----- > From: Stanley Bradbury [mailto:[EMAIL PROTECTED] > Sent: Monday, February 27, 2006 1:13 PM > To: Derby Discussion > Subject: Re: Derby Function > > Max Ten wrote: > > > Is there any function for - GROUP BY YEAR(), GROUP BY MONTH(), GROUP > > BY QUARTER() in derby? > > Hi - > There are functions but they are not supported in a GROUP BY clause in > Derby. The workaround (a bit kludgy) is to translate the values > returned by the function to table data values. I do this by creating a > table YrMnDy with a column that list all years in my dataset (col Yr) > and in the column Mn 1-12 and the column Dy 1-31 then using this query > > select label, y.Yr, m.Mn, d.Dy, count(*) > from myData, RptYMD y, RptYMD d, RptYMD m > where year(postdate) = y.Yr > and month(postdate) = m.Mn > and day(postdate) = d.Dy > group by label, y.Yr , m.Mn, d.Dy > > If you don't like aliasing the same table repeatedly or have a lot more > years to list than 31 you may find it cleaner to use different tables > for Yr, Mn and Dy. > > Hope this helps.
