You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Robert Enyedi <ro...@intland.com> on 2006/09/15 17:47:31 UTC
GROUP_BY with user functions
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 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
Re: GROUP_BY with user functions
Posted by Robert Enyedi <ro...@intland.com>.
No problem. Thanks anyway!
Regards,
Robert
Øystein Grøvlen wrote:
> Robert Enyedi wrote:
>> Thanks for the tip. I tried this, but it has the same problem with
>> alias referencing.
>
> Sorry, I should have tried this first. Fernanda has given you the
> right solution.
>
> --
> Øystein
>
Re: GROUP_BY with user functions
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Robert Enyedi wrote:
> Thanks for the tip. I tried this, but it has the same problem with alias
> referencing.
Sorry, I should have tried this first. Fernanda has given you the right
solution.
--
Øystein
Re: GROUP_BY with user functions
Posted by Robert Enyedi <ro...@intland.com>.
Wow, this really works. Thanks a lot, Fernanda!
I just wonder why the alias propagation works well in this scenario and
with the obvious one it does not.
Regards,
Robert
Fernanda Pizzorno wrote:
> Have you tried this?
>
> SELECT my_value
> FROM
> (SELECT MY_USER_FUNCTION(t1.field1) AS my_value
> FROM T1) AS UserFunction
> GROUP BY my_value
>
> Regards,
> - Fernanda
>
>
> Robert Enyedi wrote:
>> Thanks for the tip. I tried this, but it has the same problem with
>> alias referencing.
>>
>> Regards,
>> Robert
>>
>> Øystein Grøvlen wrote:
>>> Robert Enyedi wrote:
>>>
>>>> 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 have not tried this, but maybe something like this will work:
>>>
>>> SELECT UserFunction.value AS my_value
>>> FROM
>>> (SELECT MY_USER_FUNCTION(t1.field1) AS value
>>> FROM T1) AS UserFunction
>>> GROUP BY my_value
>>>
>>> --
>>> Øystein
>>>
>>
>
>
Re: GROUP_BY with user functions
Posted by Fernanda Pizzorno <Fe...@Sun.COM>.
Have you tried this?
SELECT my_value
FROM
(SELECT MY_USER_FUNCTION(t1.field1) AS my_value
FROM T1) AS UserFunction
GROUP BY my_value
Regards,
- Fernanda
Robert Enyedi wrote:
> Thanks for the tip. I tried this, but it has the same problem with
> alias referencing.
>
> Regards,
> Robert
>
> Øystein Grøvlen wrote:
>> Robert Enyedi wrote:
>>
>>> 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 have not tried this, but maybe something like this will work:
>>
>> SELECT UserFunction.value AS my_value
>> FROM
>> (SELECT MY_USER_FUNCTION(t1.field1) AS value
>> FROM T1) AS UserFunction
>> GROUP BY my_value
>>
>> --
>> Øystein
>>
>
Re: GROUP_BY with user functions
Posted by Robert Enyedi <ro...@intland.com>.
Thanks for the tip. I tried this, but it has the same problem with alias
referencing.
Regards,
Robert
Øystein Grøvlen wrote:
> Robert Enyedi wrote:
>
>> 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 have not tried this, but maybe something like this will work:
>
> SELECT UserFunction.value AS my_value
> FROM
> (SELECT MY_USER_FUNCTION(t1.field1) AS value
> FROM T1) AS UserFunction
> GROUP BY my_value
>
> --
> Øystein
>
Re: GROUP_BY with user functions
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Robert Enyedi wrote:
> 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 have not tried this, but maybe something like this will work:
SELECT UserFunction.value AS my_value
FROM
(SELECT MY_USER_FUNCTION(t1.field1) AS value
FROM T1) AS UserFunction
GROUP BY my_value
--
Øystein
Re: GROUP_BY with user functions
Posted by Robert Enyedi <ro...@intland.com>.
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
> <ma...@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
> <ma...@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
>
>
>
Re: GROUP_BY with user functions
Posted by Manish Khettry <ma...@gmail.com>.
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 <de...@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 <ro...@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 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
>
>
>
Re: GROUP_BY with user functions
Posted by Suavi Ali Demir <de...@yahoo.com>.
It seems you want all the repeating values next to each other? Would a sort help? Does order by work?
Regards,
Ali
Robert Enyedi <ro...@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 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