When I try to run any breackdown reports I get an error:
Error Type: IntegrityError
Error Value: ERROR: Unable to select an aggregate function sum(boolean) /*PostgreSQL version*/ select to_char(r_entered,'WW') as dgroup, date(r_entered) as entered, to_char(r_entered,'DD-MM-YYYY') as day, sum(r_status) as numopen, count(*) as count from request group by 1,2,3 order by 2 desc;
Other reports works well.
I'm using f2w 1.4.3
Any help will be apreciated.
Thank you,
Felix Enescu.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi
Sounds like you haven't got the latest SQL definitions installed
(the 1.4 ones)
Your copy of functions.sql needs to have the following definitions in it (and you need to have applied them to the f2w database):
create function cond_increment(int,bool) returns int as
'select case when $2 then $1 + 1 else $1 end'
language 'sql' with(iscachable);
create aggregate sum
(
basetype=bool,
sfunc1=cond_increment,
stype1=int,
initcond1=0
);
Then postgresql will sum booleans as though 't'=1 and 'f'=0.
If that's not the problem, I'll need some more details but since I'm taking a break from developing f2w just now, I'm only trying to fix things that are not too complicated.
scav
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
When I try to run any breackdown reports I get an error:
Error Type: IntegrityError
Error Value: ERROR: Unable to select an aggregate function sum(boolean) /*PostgreSQL version*/ select to_char(r_entered,'WW') as dgroup, date(r_entered) as entered, to_char(r_entered,'DD-MM-YYYY') as day, sum(r_status) as numopen, count(*) as count from request group by 1,2,3 order by 2 desc;
Other reports works well.
I'm using f2w 1.4.3
Any help will be apreciated.
Thank you,
Felix Enescu.
Hi
Sounds like you haven't got the latest SQL definitions installed
(the 1.4 ones)
Your copy of functions.sql needs to have the following definitions in it (and you need to have applied them to the f2w database):
create function cond_increment(int,bool) returns int as
'select case when $2 then $1 + 1 else $1 end'
language 'sql' with(iscachable);
create aggregate sum
(
basetype=bool,
sfunc1=cond_increment,
stype1=int,
initcond1=0
);
Then postgresql will sum booleans as though 't'=1 and 'f'=0.
If that's not the problem, I'll need some more details but since I'm taking a break from developing f2w just now, I'm only trying to fix things that are not too complicated.
scav
You are right. I dont have the last SQL.
But meanwhile I just edited every report replacing
"sum(r_status)" with "sum(case when r_status then 1 else 0 end)"
May be I'll aply latest sql.
Thanks a lot for your answer.
Felix.