group by time(date_col)

building reports desktopintelligence
1

I have a query with an aggregate column (AVG(…)) and a YEAR(…) column in the select clause and I would like to have a GROUP BY YEAR(…) clause. This works in DB2, but only if I use a subquery such as : select hireyear, avg(salary)
from (select year(hiredate) as hireyear, salary
from emp) as tmp
group by hireyear

I would like to know if it’s possible to generate such a query with BO (4.1.3) ???
Reason : if the query is written as select year(hiredate), avg(salary) from emp, then BO generates a group by clause like group by year(hiredate) which is not accepted by DB2 !

Thanks for any information
Pierrot

__________________________________________________________ Pierrot HERITIER
Swiss Federal Railways, Mittelstrasse 43, CH-3030 Bern (Switzerland) Tel (++41) (512) 20 4484
Fax (++41) (512) 20 4485
e-mail : pher@royal.net

Listserv Archives (BOB member since 2002-06-25)

2

We are also having same problem.

But we have decided to use views.
Basically we will be replacing the sub-query by a view.

– Vasan

From: pher@ROYAL.NET

I have a query with an aggregate column (AVG(…)) and a YEAR(…) column in the select clause and I would like to have a GROUP BY YEAR(…) clause. This works in DB2, but only if I use a subquery such as : select hireyear, avg(salary)
from (select year(hiredate) as hireyear, salary
from emp) as tmp
group by hireyear

I would like to know if it’s possible to generate such a query with BO (4.1.3) ???
Reason : if the query is written as select year(hiredate), avg(salary) from
emp, then BO generates a group by clause like group by year(hiredate) which
is not accepted by DB2 !

Thanks for any information
Pierrot

__________________________________________________________ Pierrot HERITIER
Swiss Federal Railways, Mittelstrasse 43, CH-3030 Bern (Switzerland) Tel (++41) (512) 20 4484
Fax (++41) (512) 20 4485
e-mail : pher@royal.net

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (9am-5pm ET only): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)