Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Reply-To: <msegel@segel.com>
From: <derby@segel.com>
Sender: "Michael Segel" <msegel@segel.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: Derby Function
Date: Mon, 6 Mar 2006 12:26:37 -0600
Organization: MSCC
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
In-Reply-To: <5F3EBBC316C84F49A35B29023D459932E9125F@SLNTEXG.quadrian.com>
Thread-Index: AcZAhXuOpTSxCwnoTJGsvSt02JsjvgAwDyeg
Message-Id: <20060306182913.66F964A5D8@dbrack01.segel.com>

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: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.