Row count

[catrmm]

I'd like to provide the capability for users to create queries that bring back just a limited number of rows.Ã,  I'dÃ,  like to put in a filter so they can specify how many rows they'd like.Ã,  It's a UDB version 8 database.Ã,  The DB2 has a row_number() function.Ã,  It also has a fetch first rows only.Ã,  How do I get Cognos to use either of these?Ã,  I've found the function running-count in my version of Cognos but that takes an inordinant amount of time.Ã,  I don't seem to have rownum() or rownumber() that I've seen referenced elsewhere.Ã,  Is there an alternate function I've not found yet?

[blom0344]

The OLAP function Row_number() can be used to restrict the number of rows returned, but only if you use it in an inline view, like:

Code Select Expand


SELECT * FROM
(SELECT FIELD1,FIELD2,ROW_NUMBER() OVER (ORDER BY FIELD3) AS R
FROM TABLE WHERE ................) XXX
WHERE R <= 1000
ORDER BY FIELD3;


Which may be usefull if you can build a prompt that stores the value (in this case 1000)