Using CASE statement and aggregate fn in BO 1

[legend8]

I'm using BO and DB2 for my reports. If I'm using a CASE..WHEN..END statement and aggregate function in the SELECT clause, BO will automatically generate an SQL with

SELECT .... CASE..WHEN..END,...COUNT(*)....
FROM ....
WHERE ....
GROUP BY ... CASE..WHEN..END

which fails because DB2 does not allow any function in the GROUP BY clause. Is there any way I can make BO to generate a correct SQL for DB2?

 

[TheVMan]

I'm not familiar with DB2, but in the RDBMS I use I take advantage of the ability of the fact that I can say
CASE WHEN WHATEVER
END (NAMED COLUMN)

That way you can reference it in your group by. Alternatively, my RDBMS allows me to reference a column selected by 1,2,3, etc. Get it? Give it a shot.

V

 

[legend8]

Unfortunately, DB2 does not allow you to use the name of the column.
Thanks for the info anyway.

 

[TheVMan]

Legend,
Did referencing the column numerically, based on the order you selected it work for DB2. Just curious where you got on this one. Thanks.

V

 

[legend8]

Hi V,
The problem I have is using DB2 with BO. I've a SQL with CASE...WHEN...END and aggregrate function (eg. SUM). When BO try to create the SQL, it use whatever in the SELECT clause and place them in the GROUP BY clause and DB2 syntax does not allow this.

For example,
SELECT A, B, (CASE WHEN C THEN 1 ELSE 0 END), SUM(*)
FROM TAB1
GROUP BY A, B, (CASE WHEN C THEN 1 ELSE 0 END)

and DB2 does not allow any function in GROUP BY.

 

[BobWard]

How about if you created a view which had the case statement as a view. Would db2 then allow the group by basd on the column in the view ??

 

[legend8]

That's an alternative. I just don't understand why BO only has one set of rules to create the SQL. They should have build an SQL based on the DBRMS.

 

[BobWard]

they do hsve seperate sql per rdbms, its configurable in the *.prm file in the data access directories, Im not sure you be able to configure what you want to do though.

 

[bensonpa]

Seen this once before. It's some sort of limitation of DB2 (certain versions only) and in order to get around it we had to remove all functions from the SQL.

 

[sriramna]

The "Group By" clause is supported for "derived" values (i.e. columns which are not table fields) from OS/400 release V4R4 for DB2.