db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Derby Function
Date Mon, 06 Mar 2006 18:26:37 GMT
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...


> -----Original Message-----
> From: Alex Miller [mailto:amiller@metamatrix.com]
> 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: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