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?
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:
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)