an error in RS when I try to aggregate a field

[gregoryb]

 down vote favorite
   

I haver cognos on windows server connecting to posgres via postgreSQL ODBC. I created a report in RS. Whenever I try adding a numeric field to the report, i get this error:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'. UDA-SQL-0107 A general exception has occurred during the operation "prepare". No query has been executed with that handle

If I change the field's Aggregate Function to 'None', everything works fine.

Thanks for any leads you can give!

[blom0344]

I know nothing about postgreSQL, but it may have problems with just an aggregate in the query. Normally one would build a query based on aggregates in the context of non-aggregates, like quantities sold per customer. Only fetching 1 aggregated item would always yield 1 meaningless figure. How about building a query that represents a dataset for a report?

[gregoryb]

I tried doing it with an aggregate, without an aggreagate.. and many other variables. same result.
So I left it open and went home. This morning I pressed the validate report button, and everything works.
I'm new to Cognos.. but it's starting to seem like a pretty unstable tool.

[blom0344]

validate report does NOT validate the queries defined!!  It actually tests the report xml, which does not contain the query definitions.  Cognos is far from unstable, it is a little unfair to accuse it while getting accustomed to it..

We are all quite dedicated to it, though as any tool it has its quirks. If you keep the discussion on a technical level, we'll be able to give proper advice..

[gregoryb]

Sorry blom, It's just frustrating. I spent a lot of time trying to get to the bottom of what was wrong to try and fix it.. and then it suddenly fixed itself somehow. I'm still afraid that the error might return. But you are right, no piece of software is without its problems.

As for validating the report -- yesterday, when the error was happening, the validation would fail (giving me the error). I would also get the error if I tried viewing the generated query (through tools->show generated SQL). So I don't know what to make of this.
I guess it won't be possible to debug it now, since the error no longer occurs (for now, at least).

[blom0344]

Sorry to hear that you are off to a bumpy start.  ODBC is not the best way to connect to a database cause it's  seriously limited compared to native connectivity.

In general Cognos has a very extensive way of giving feedback on errors of any kind, though sometimes hard to decrypt. SQL errors are usually handled very well.

Prior to running the report itself you can very easily test the output generated by SQL by running tabular data. If you cannot view the SQL proposed, then the statement cannot be 'compiled'.  I used Cognos on every major database , but never experienced the problem you describe. I'd rather suspect connectivity / database then Cognos in this case..