Expression in GROUP BY clause

[aparnaramsharma]

Hi all,

I am using DB2 7.1.1 on OS/390.

When I issue the following query:
select emplid, p1_alot_hours*5, sum(p1_alot_hours)
from
ps_p1_abs_accrual
where emplid = '100334'
group by emplid, p1_alot_hours*5
------------------------------------------------------------

I get the following error:
SQL0104N An unexpected token "*" was found following "". Expected tokens may
include: "FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE ".
SQLSTATE=42601

What could be the reason for the error? Does not DB2 on Mainframes support calculations in the group by clause. Is there any setup for enabling this feature.

Thanks in advance for the responses.

Aparna

 

[MarcLodge]

Aparna,
You'll find that DB2 doesn't like maths in the group by statement. Try removing it so that the SQL reads:
select emplid, p1_alot_hours*5, sum(p1_alot_hours)
from
ps_p1_abs_accrual
where emplid = '100334'
group by emplid, p1_alot_hours

Marc

 

[aparnaramsharma]

Hi Marc,
Thanks for your reply. I tried to group as you have said, and it work fine. But my problem is - I am using a tool and I do not want to modify the SQL the tool has generated. When I pick up the calculated field and sum() field, the tool automatically groups by all the fields other than the sum field, which is emplid and p1_alot_hours*5 in this case. Any other way to achieve this, as this way of grouping is supported in SQL Server and Oracle.
Thanks,
Aparna

 

[blom0344]

any chance you are using a BI tool like Business Objects?
A simple way around this is to create a report-variable that holds 5*p1_alot_hours.
Hope that other tool you are using has the same sort of functionality?

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[aparnaramsharma]

Hi Blom,
Yes. I am using Business objects and created a report variable and it works. We just wanted a confirmation if it is problem with DB2 so that directly we cannot use the object created, in the report. We wanted to inform the user what objects can be directly used and what have to be calculated fields in the report.
Thanks for the reply,
Aparna

 

[blom0344]

Rule of thumb for Business objects is that measures are used as aggregates. Using both measures AND aggregated measures will net you 'funny' resultsets.

In your example you use both a non-aggregate and an aggregate which enforces the non-aggregate into he group-by clause. Using only aggregates will solve the problem in any case...

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[PruSQLer]

BTW, this expression in the Group By clause works fine in DB2 Vers 7 running on UNIX.