Framework Manager ignores Cognos SQL 'order by'

[kahntt]

HI there,

I've got a bit of a problem sorting a simple query based on one table. The table consists of an ID, a foreign_key_ID, a timestamp and assorted others. I want to sort the table by the foreign key ID and descendig timestamp (in that order). The table contains the history of steps for specific workflows(foreign_key). To determine the elapsed time for each step (excluding the newest) Icompute the timestamp to a number and use the running-difference function with "for foreign_key_id". The computation works well except that the result has no meaning for unsorted datasets.
If I open the query response I get a warning (QE-DEF-0469/RQP-DEF-0258) that sorting is not allowed with nested selects. But there is only one table and only one select as fas as I can see.

Any suggestions?

Thank you

[mikegreen]

What happens if you run the query directly against the database?  What RDMS system is it?

[kahntt]

The RDBMS is Oracle 9i (9.2.0.7). If I run th query in e.g. sqlplus the results are exactly what I want them to be...

[vetteheadracer]

I have had exactly the same problem with SQL Server too. It appears that whatever keys are set up it will sort in that order rather than using the Order By variable in the query.

[sir_jeroen]

Is there any grouping in the query? Grouping may also cause a sorting...
If there's no sorting/grouping in the query how is the index set up for the table?
If it's a clustered index then the data will be stored in that order.... So that may be the cause...

[kahntt]

The table consists of only three indexes: one used as primary key and two used as foreign keys. The primary key is fairly standard: NORMAL UNIQUE index on the table ID field. The only difference to the others is that they are NONUNIQUE. The indexes are not partitioned, temporary or anything else, just every-day-indexes. The query in Cognos has no grouping only the special 'order by' argument to sort the entries by date to use some row-to-row-functions for pre-computation of values in the model. :-\

[lindero]

Hey C8 users,

I have made the experience that there is a difference between sorting on result page and pre-sorting in the query directly. If you set the property pre-sort then it becomes effective in the genereated SQL, otherwise it is sorted on the local report server. That can take serveral minutes caused by the size of the resulset.

I hope this helps you.

regards,
Oliver

[kahntt]

Please be aware of the fact that we're talking about the query subject in Framework Manager. The query has no property pre-sort other than in Report Studio. My intention is a near complete data preparation for report administrators that they don't have to build the algorithms for e.g. elapsed time between two record reffering the same entity (e.g. document change history of a knowledge base).