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.
