GROUP BY clause for DB2

system · June 15, 1998, 11:53am

Is anybody using DB2 (Version 5) instead of Oracle as database ? We are trying it and I got a problem : BO seems to generate a wrong query, especially the GROUP BY clause, when one of the SELECT variable has a built-in function applied.
Example :
select name, sum(salary+comm)
from q.staff
group by name

is OK. BUT

select substr(name,1,3), sum(salary+comm) from q.staff
group by substr(name,1,3)

is not accepted by DB2. The GROUP BY syntax accepts only column name, not functions. Note that the problem occurs with all other functions such as year(), month() etc. which is very annoying.

Has anybody a workaround or an idea ?
TIA

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)

system · June 15, 1998, 1:06pm

is not accepted by DB2. The GROUP BY syntax accepts only column name, not functions. Note that the problem occurs with all other functions such as year(), month() etc. which is very annoying.

Ah, one of the most annoying restrictions of all in DB2. The non-aggregated values must be an unambiguous column name. This of course has nothing to do with BusinessObjects. If you are not working with large amounts of detail then you can let the automatic aggregation of BusinessObjects take care of it. Otherwise don’t use anything but column names.

Thanks,

Greg Mills

s-greg.mills@usa.conoco.com
ETN/DUCOM 442-5597
(580) 767-5597

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

system · June 15, 1998, 1:38pm

Von: Pierrot Heritier [SMTP:pher@ROYAL.NET] Gesendet am: Montag, 15. Juni 1998 13:54 An: BUSOB-L@LISTSERV.AOL.COM
Betreff: GROUP BY clause for DB2

Is anybody using DB2 (Version 5) instead of Oracle as database ? We are trying it and I got a problem : BO seems to generate a wrong query, especially the GROUP BY clause, when one of the SELECT variable has a built-in function applied.
Example :
select name, sum(salary+comm)
from q.staff
group by name

is OK. BUT

select substr(name,1,3), sum(salary+comm) from q.staff
group by substr(name,1,3)

is not accepted by DB2. The GROUP BY syntax accepts only column name, not functions. Note that the problem occurs with all other functions such as year(), month() etc. which is very annoying.

Do you use V4.1.2? In this version the DB2-V5 Middleware is production, perhaps in this version your problem is solved…

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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

system · June 15, 1998, 1:53pm

At 15.06.98 08:06, you wrote:

is not accepted by DB2. The GROUP BY syntax accepts only column name, not functions. Note that the problem occurs with all other functions such as year(), month() etc. which is very annoying.

Ah, one of the most annoying restrictions of all in DB2. The non-aggregated values must be an unambiguous column name. This of course has nothing to do with BusinessObjects.

Nothing to do with BO ??? BO generates a query with a GROUP BY clause that is wrong with DB2, and this has nothing to do with BO ??? The error I get is an SQL -104 (kind of syntax error). The real problem is that BO generates an SQL for Oracle and that this SQL is wrong for DB2. This is a real bug of BO, not of DB2. (I’m using BO 4.1.2). I agree that DB2 should accept this kind of GROUP BY, but I did not see this possibility in the ‘what’s new in version 6’.

If you are not working with large amounts of detail then you can let the
automatic aggregation
of BusinessObjects take care of it. Otherwise don’t use anything but column names.

Thanks,

Greg Mills

__________________________________________________________ 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)

system · June 15, 1998, 3:20pm

Von: Pierrot Heritier [SMTP:pher@ROYAL.NET] Gesendet am: Montag, 15. Juni 1998 15:53 An: BUSOB-L@LISTSERV.AOL.COM
Betreff: Re: GROUP BY clause for DB2

…

Nothing to do with BO ??? BO generates a query with a GROUP BY clause that is wrong with DB2, and this has nothing to do with BO ??? The error I get is an SQL -104 (kind of syntax error). The real problem is that BO generates an SQL for Oracle and that this SQL is wrong for DB2. This is a real bug of BO, not of DB2. (I’m using BO 4.1.2). I agree that DB2 should accept this kind of GROUP BY, but I did not see this possibility in the ‘what’s new in version 6’.

Check the DB2xxx.PRM file, there is a parameter called GROUPBYCOL=NO Try to set this to YES.

Walter

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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

system · June 15, 1998, 4:15pm

Nothing to do with BO ??? BO generates a query with a GROUP BY clause that is wrong with DB2, and this has nothing to do with BO ???

Well BO generates a GROUP BY clause because the definition of one of your objects has a SUM function defined in the SQL. So if you don’t want BO to generate GROUP BY clauses don’t pick objects defined with SQL SUM (or other aggregate) functions. I hardly see how this is BO’s fault. You are defining objects and combining them in ways which DB2 cannot handle. What do you want BO to do, change the definition of your objects or read your mind???

Either you have SUM objects combined with objects which are unambiguous column names or you DON’T have SUM objects and let BO aggregate them in the report. BusinessObjects can’t read your mind.

The only caveat I have is that I don’t know anything about V5 but I don’t think the problem is solved.

Thanks,

Greg Mills

s-greg.mills@usa.conoco.com
ETN/DUCOM 442-5597
(580) 767-5597

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

system · June 16, 1998, 5:55am

Well BO generates a GROUP BY clause because the definition of one of your objects has a SUM function defined in the SQL. So if you don’t want BO to generate GROUP BY clauses don’t pick objects defined with SQL SUM (or other aggregate) functions. I hardly see how this is BO’s fault. You are defining objects and combining them in ways which DB2 cannot handle. What do you want BO to do, change the definition of your objects or read your mind???

It’s not a problem of what I want to do or not. It’s a problem that BO generates a query as if the target system were Oracle (that supports grouping by function) and this is wrong. There is an IF clause missing in the generation program of BO to test which is the target system. BO cannot simply say ‘well I generate what I want and I don’t care which target system you use’ ! If DB2 would return wrong data, I would clearly say that it’s MY problem. But DB2 returns an SQL Error -104 which is a syntax error problem, and this is not MY problem, it’s a BO problem.

For Walter : I’ve already tried to set GROUPBYCOL=YES, but this way BO Generates the line GROUP BY 1,2, which is also not accepted by DB2.

Thanks you both for taking time to help me 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)

system · June 16, 1998, 8:11am

Pierrot Heritier wrote:

It’s not a problem of what I want to do or not. It’s a problem that BO generates a query as if the target system were Oracle (that supports grouping by function) and this is wrong. There is an IF clause missing in

I do not understand, how DB2 handles your case. How would the SQL look like if you wrote it directly to DB2 without having BusinessObjects? Could you do this at all? If not, then the object definitions (which contain function calls) must be redefined, i.e in this case you must either use local report functions (substring, etc) to get the corresponding values, or, store them directly in the database as single columns. I agree, both cases would also not satisfy me…BusinessObjects assumes that all functions which do aggregation (like sum, avg, min, max, etc) NEED a GROUP BY all other (=non aggregated) objects (columns in the query). This is independent of the database system in use, and, normally is required in SQL.

the generation program of BO to test which is the target system. BO cannot simply say ‘well I generate what I want and I don’t care which target system you use’ ! If DB2 would return wrong data, I would clearly say that it’s MY problem. But DB2 returns an SQL Error -104 which is a syntax error problem, and this is not MY problem, it’s a BO problem.

Getting SQL errors is not only a DB2 issue, you can easily create objects which will give you wrong SQL, even in Oracle. As far as the “generation” algorithm of SQL is concerned, this is much more complex as simply putting an IF… into it. Actually, SQL IS generated DIFFERENTLY, according to the target database (e.g. look at outer join generation).

Walter

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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

system · June 16, 1998, 8:23am

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

system · June 16, 1998, 12:31pm

Pierrot wrote:

Is anybody using DB2 (Version 5) instead of Oracle as database ? We are trying it and I got a problem : BO seems to generate a wrong query, especially the GROUP BY clause, when one of the SELECT variable has a built-in function applied.

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

system · June 16, 1998, 2:28pm

Here is the example Pierrot used:

select substr(name,1,3), sum(salary+comm) from q.staff
group by substr(name,1,3)

DB2 will not accept that. DB2 will accept the following:

select substr(name,1,3), sum(salary+comm) from q.staff
group by name

Let’s assume you have the same tables on Oracle (or Sybase) and DB2. The first query will run on Sybase and Oracle. It will not run on DB2. The second query will run on DB2. However, and this is a BIG however, the second query will NOT return the same data on DB2 as it did on the other two. It is fundamentally a different request and if for example you put criteria such as a “having” clause, you are in danger of making decisions based upon missing data.

In my opinion when you put those two objects in a query you are requesting something different than the resulting SQL in the second example above. You are making a request DB2 cannot handle and it is your substring and sum sql that is incorrect. DB2 simply cannot produce the results that Sybase and Oracle can in this case. Putting in a group by that is different than the object in the select is changing the user request. I don’t think this is just a syntax issue. If the same universe and the same table structure and the same data, produce DIFFERENT results that is scary. To change the SQL for DB2 is to change the actual nature of the request. Maybe it should be requested as an option in the DB2 prm file to let each site choose how they want this handled. We wouldn’t change the way it works now.

That may be what you want in a particular case Pierott, but I would be very careful.

Thanks,

Greg Mills

s-greg.mills@usa.conoco.com
ETN/DUCOM 442-5597
(580) 767-5597

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

system · June 16, 1998, 3:31pm

Mills, S. Greg wrote:

Here is the example Pierrot used:

select substr(name,1,3), sum(salary+comm) from q.staff
group by substr(name,1,3)

DB2 will not accept that. DB2 will accept the following:

select substr(name,1,3), sum(salary+comm) from q.staff
group by name

Thanks, Greg
I completely agree with you on the consequences of this different request…

WM

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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

system · June 17, 1998, 3:04am

In a message dated 98-06-16 01:56:17 EDT, you write:

It’s not a problem of what I want to do or not. It’s a problem that BO
generates a query as if the target system were Oracle (that supports grouping by function) and this is wrong.

Ah, but it is not Oracle SQL that is being generated by Business Objects… it is just SQL. There are many databases other than Oracle - most of them, in fact - that support grouping by a function. You can check this by creating a similar query in MS Access; grouping by a function is allowed there. Sybase, Informix, SQL Server… all of these allow this feature to a certain degree. This feature is, in fact, part of the ANSI Standard definition for the SQL language.

The root of the problem is that DB2 is very much behind in implementing Standard SQL. They only recently added outer joins!

So, possible solutions could be

create objects that are valid at all times (which would mean eliminating the sum() objects)
create contexts that prevent objects with functions from being combined with aggregate objects
try some tricks with Aggregate Awareness / incompatible objects 4. train your users what they can and cannot do.

You say that you feel this is a problem because BusObj does not check for DB2 before generating the SQL… how would you write the SQL any differently? What would you want BusObj to do in your case? What would be a proper solution? After you answer those questions, perhaps you can recreate your objects to support your solution.

Given the restrictions of DB2 there does not appear to be a way to generate the correct SQL given the current logic in your objects.

A new thought… have you considered building your report using two queries? I no longer have your original question, but you were looking at something like:

select substr(x), sum(y)
from tab
group by substr(x)

… which caused a DB2 syntax error. You could create a report using two queries, perhaps, that would look something like this:

(Query 1)
select x, sum(y)
from tab
group by x

(Query 2)
select x, substr(x)
from tab

Query 2 does not require a GROUP BY because there are no other Group functions. The common object ‘x’ would be the “link” column between your two queries. I’m not sure whether this is more intuitive for users than simply removing the sum() from object y and letting BusObj do the aggregation on the client side.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in '98!

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

system · June 17, 1998, 6:00am

There are opinions, and there are facts. I believe that the problem is clear for everybody that DB2 and Oracle (and other maybe, I don’t know them) handle the GROUP BY differently. This is a fact. Another fact is that DB2 has a 2-stage GROUP BY function : the two queries below give exactly the same result (luckily !) if you use the GROUP/SUM functions in the FORM in DB2; in other words, the GROUP BY NAME produces data grouped by NAME, and you have to use a second GROUP BY in the FORM of DB2 to have the resulting data grouped by SUBSTR(NAME,1,3). This is how DB2 work and this is a fact. My OPINION and yours too is that the Oracle-style query is much better, I completely agree with you. But I have to work with DB2 and my opinion is that BO generates a query that work as DB2 should, but not as DB2 does. And this is an error.
Let’s say BO would have been developed on a DB2 basis and would ALWAYS generate a DB2-based query (i.e. with GROUP BY NAME). What would be the reaction of Oracle users (NB : we use both !) ? Standard SQL does not exist, there is only a subset of SQL that is used by all DBMS. If we want the standard SQL, then we use ODBC and not SQL*Net or DB2 Connect. BO generates queries for Oracle, and some of them are also supported by DB2.
But the facts is that the target DBMS cannot simply be ignored by the BO Query Generator !
BO is now partner #1 of IBM and I bet that these compatibility problems will be fixed in the next year (but this is of course an opinion !)

Thanks to Walter Muellner, Glenn Fredericks, Greg Mills, Atli Gumundsson, Dave Rathbun who all helped finding a solution to this problem.

Pierrot

Here is the example Pierrot used:

select substr(name,1,3), sum(salary+comm) from q.staff
group by substr(name,1,3)

DB2 will not accept that. DB2 will accept the following:

select substr(name,1,3), sum(salary+comm) from q.staff
group by name

Let’s assume you have the same tables on Oracle (or Sybase) and DB2. The first query will run on Sybase and Oracle. It will not run on DB2. The second query will run on DB2. However, and this is a BIG however, the second query will NOT return the same data on DB2 as it did on the other two. It is fundamentally a different request and if for example you put criteria such as a “having” clause, you are in danger of making decisions based upon missing data.

In my opinion when you put those two objects in a query you are requesting something different than the resulting SQL in the second example above. You are making a request DB2 cannot handle and it is your substring and sum sql that is incorrect. DB2 simply cannot produce the results that Sybase and Oracle can in this case. Putting in a group by that is different than the object in the select is changing the user request. I don’t think this is just a syntax issue. If the same universe and the same table structure and the same data, produce DIFFERENT results that is scary. To change the SQL for DB2 is to change the actual nature of the request. Maybe it should be requested as an option in the DB2 prm file to let each site choose how they want this handled. We wouldn’t change the way it works now.

That may be what you want in a particular case Pierott, but I would be very careful.

__________________________________________________________ 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)

system · September 3, 2008, 11:10am

Again about Informix database
We have Informix 2000 and there group by function is supported.
But only with ordinal number in group by clause.
How to force BO to generate that kind of query?

BO generates this SQL, which is failing:
SELECT
year(row_date),
sum(acdcalls)
FROM hagent
WHERE row_date=today-10
group by year(row_date)

This one is working:
SELECT
year(row_date),
sum(acdcalls)
FROM hagent
WHERE row_date=today-10
group by 1

travian (BOB member since 2007-12-05)

system · September 3, 2008, 2:03pm

There is a parameter that is supposed to control this behavior. Depending on the version of Designer you’re using, it will either be edited directly in the universe parameters or via the .prm text file.

The parameter name in XI looks like this:

<Parameter Name="GROUPBYCOL">NO</Parameter>

Try changing the value to YES and you should see a group by 1, 2 rather than by column names. I tested updating the parameter file directly and it worked. You may also be able to update / override this parameter within the universe, but I didn’t test that.

Dave Rathbun (BOB member since 2002-06-06)

system · October 29, 2010, 2:52pm

This almost 12 years after the topic was first posted, and I guess DB2 has moved on since then…
In any case this problem is now solved in DB2 (just in case someone like me is in a similar situation and comes accross this post)…

so for example you could:

select Day(SomeDate), Month(SomeDate), Sum(TotalAmount)
from SomeTable
Group By Day(SomeDate), Month(SomeDate)

I have tested this in db2 / as400 and works fine

arnab.mitra (BOB member since 2010-10-29)