Stored procedure in GROUP BY

[Former Member]

Hello,

I'm using BO 6.5 connected to Informix 7.0 database. Some objects I've created are based on stored procedures. However, when I create query which includes a mesure, BO returns syntax error as stored procedure code is in GROUP BY list. The situation more or less looks like this:

SELECT X, stored_procedure(Y), sum(Z)

FROM some_table

WHERE X >0

GROUP BY

X,

stored_procedure(Y)

Can anyone tell me how to replace a stored procedure from GROUP BY list by, for instance, number of column? (for example: GROUP BY 1, 2)?

Thanks in advance,

Gregory

[Former Member · Accepted solution]

Hi Gregory,

I have not tried to use sps in my views like you are so I am not sure if this will solve your problem. To get rid of the GROUP BY you can try changing a Universe Parameter. The property is DISTINCT_VALUES, if you set this to DISTINCT, then the group by should not be created.

Hope this helps

Regards

Alan

[Former Member · Accepted solution]

Hello Alan,

I have tried to change DISTINCT_VALUES parameter either to DISTINCT and GROUPBY values. However, this did not change the SQL code generation while using a stored procedure object and a measure simultaneously. I guess BO always generates GROUP BY clause when a query contains any aggregation function as it is "forced" by SQL syntax rules. I managed to ommit this problem by replacing GROUP BY list by number of columns before query execution. However, there might be people in my company who are going to use this universe wtihout knowing sql...

But thanks anyway for your help.

Gregory