The Derby table-result function syntax requires all output columns to be declared as part of the function definition, and more importantly it does not seem to allow calls into Derby itself to get results. So this would not seem to be a viable option for that reason.
Back to square 1, I guess. Derby doesn't seem to allow any way to declare aggregate functions either, so I couldn't declare a FIRST() aggregate method as proposed below. Simple arithmetic functions seem like they would work, but that's not helpful here. Karl On Sat, Sep 18, 2010 at 6:45 AM, Karl Wright <daddy...@gmail.com> wrote: > 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 >> >
