Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: kahntt on 15 Nov 2006 06:02:36 AM

Title: Framework Manager ignores Cognos SQL 'order by'
Post by: kahntt on 15 Nov 2006 06:02:36 AM
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
Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: mikegreen on 20 Nov 2006 09:31:08 AM
What happens if you run the query directly against the database?  What RDMS system is it?

Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: kahntt on 23 Nov 2006 01:49:14 AM
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...
Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: vetteheadracer on 27 Nov 2006 07:45:25 AM
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.
Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: sir_jeroen on 28 Nov 2006 12:56:04 PM
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...
Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: kahntt on 29 Nov 2006 05:36:12 AM
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. :-\
Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: lindero on 29 Nov 2006 07:37:34 AM
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
Title: Re: Framework Manager ignores Cognos SQL 'order by'
Post by: kahntt on 29 Nov 2006 08:22:53 AM
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).
Text only | Text with Images