db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: GROUP BY can only be followed by a real column?
Date Tue, 03 Jun 2008 14:04:55 GMT
One way to solve this problem would be to group by the return value of a 
function which bins the time values into two buckets. Something like this:

import java.sql.*;
public class z
{
    public static int isNull( Time time )
    {
        if  ( time == null ) { return 1; }
        else { return 0; }
    }
}

The following script shows the sql needed:

create table timeTable
(
   timeValue time
);

create function isNull
(
    timeValue time
)
returns int
language java
parameter style java
no sql
external name 'z.isNull'
;

insert into timeTable
values ( null ), ( time('15:09:02') ), ( time('13:09:02') ), ( null ), ( 
null );

select t.isNull, count( t.isNull )
from
(
  select isNull( timeValue ) as isNull
  from timeTable
) t
group by t.isNull;

Hope this helps,
-Rick


sin(EaTing), wrote:
> Hi,
>
> I am trying a statement like:
> SELECT count(*) FROM table1 GROUP BY YEAR(table1.the_time);
> I found it's OK in systems like DB2. But "syntax error" was given when 
> I tried on Derby.
>
> So does it mean GROUP BY in Derby can only be followed by a real 
> column instead of some extra decoration?
> And if not, how could I do something like to group by null and not 
> null like:
> SELECT count(*) FROM table1 GROUP BY IS NULL(table1.the_time);
>
> Thanks! 


Mime
View raw message