db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Enyedi <robert.eny...@intland.com>
Subject Re: GROUP_BY with user functions
Date Mon, 18 Sep 2006 09:16:49 GMT
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* <demir4@yahoo.com 
> <mailto:demir4@yahoo.com>> 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 <robert.enyedi@intland.com
>     <mailto:robert.enyedi@intland.com>>/* 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
>
>
>


Mime
View raw message