Given the variant nature of user functions it seems perfectly justified
to not use nor allow them in the GROUP BY clause. However, I needed this
because first of all aliases cannot be referenced in the GROUP BY clause.
For instance, let's consider this query:
SELECT MY_USER_FUNCTION(t1.field1) AS MY_VALUE
FROM T1
If I need to group the values returned by the MY_USER_FUNCTION, I simply
cannot do so because the following query is invalid in Derby:
SELECT MY_USER_FUNCTION(t1.field1) AS MY_VALUE
FROM T1
GROUP BY MY_VALUE
I personally find this a serious limitation of Derby which should be
addressed ASAP for Derby to be usable for applications that require more
complex queries. This is especially true since the number of build-in
functions is limited.
So if I need user function value based grouping, what can be done?
Regards,
Robert
Manish Khettry wrote:
User functions can't be used in group by clauses although post 883 we
do allow expressions. User functions can be variant-- i.e. return
different values given the same arguments and hence not applicable
to group on.
m
On 9/15/06, *Suavi Ali Demir* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
It seems you want all the repeating values next to each other?
Would a sort help? Does order by work?
Regards,
Ali
*/Robert Enyedi <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>>/* wrote:
Does Derby support user functions in GROUP BY clauses?
I'm thinking of the following scenario:
SELECT USER_FUNCTION(t1.column1)
FROM TABLE t1
GROUP BY USER_FUNCTION(t1.column1);
When I tried this with the latest 10.2.1.3 <http://10.2.1.3>
beta version, this is the
error I get:
ERROR 42Y30: The SELECT list of a grouped query contains at
least one
invalid expression. If a SELECT list has a GROUP BY, the list
may only
contain valid grouping expressions and valid aggregate
expressions.
Any ideas?
Thanks,
Robert
