db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alex Miller" <amil...@metamatrix.com>
Subject RE: Derby Function
Date Sun, 05 Mar 2006 18:51:18 GMT
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

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

For more details on an example query just like yours (and how it works
in EA2), see my blog on the subject at

Alex Miller
Chief Architect

-----Original Message-----
From: Stanley Bradbury [mailto:Stan.Bradbury@gmail.com] 
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.

View raw message