For what it's worth, defining a Derby function seems like the only way to do it. These seem to call arbitrary java that can accept a query as an argument and return a resultset as the result. But in order to write such a thing I will need the ability to call Derby at a java level, I think, rather than through JDBC. Still looking for a good example from somebody who has done something similar.
Karl On Sat, Sep 18, 2010 at 6:28 AM, Karl Wright <daddy...@gmail.com> wrote: > Hi Folks, > > For two of the report queries, ACF uses the following Postgresql > construct, which sadly seems to have no Derby equivalent: > > SELECT DISTINCT ON (idbucket) t3.bucket AS idbucket, t3.activitycount > AS activitycount, t3.windowstart AS starttime, t3.windowend AS endtime > FROM (...) t3 > > In Postgresql, what this does is to return the FIRST entire row > matching each distinct idbucket result. If Derby had a "FIRST()" > aggregate function, it would be the equivalent of: > > SELECT t3.bucket AS idbucket, FIRST(t3.activitycount) AS > activitycount, FIRST(t3.windowstart) AS starttime, FIRST(t3.windowend) > AS endtime FROM (...) t3 GROUP BY t3.bucket > > Unfortunately, Derby has no such aggregate function. Furthermore, it > would not be ideal if I were to do the work myself in ACF, because > this is a resultset that needs to be paged through with offset and > length, for presentation to the user and sorting, so it gets wrapped > in another SELECT ... FROM (...) ORDER BY ... OFFSET ... LIMIT ... > that does that part. > > Does anyone have any ideas and/or Derby contacts? I'd really like the > quick-start example to have a functional set of reports. > > Karl >
