Trying to group by a UDF return value

By: Borland Staff

Abstract: Grouping is only allowed field values stored in a table

Problem:
I'm trying to execute the following query, but can't seem to be 
able to group by the UDF return value.

select f_striptime(date_field), sum(amount_field) from table1
group by f_striptime(date_field);

This query doesn't work either:

select f_striptime(date_field), sum(amount_field) from table1
group by 1;


Solution:
The information in this article applies to:
  * InterBase v5.x

InterBase and the SQL standard state that you can only group
by a column name.  Thus this type of query is not possible.

Here are the workarounds available:

1) create a new computed by column in the table and use that in the query:

    alter table1 add date_only computed by (f_striptime(date_field));

    select date_only, sum(amount_field) from table1
    group by date_only;

2) create a stored procedure to accomplish the task.


Server Response from: ETNASC03