To retrieve first N objects in query results

I am using Documentum Developer Edition 6.6. I need to (using DFS) execute the following DQL expression

select "r_version_label","i_chronicle_id", "i_position", "r_modify_date" , "subject","title","r_object_type","object_name","r_object_id" from "dm_document" where FOLDER (ID('0cde75d180000107')) and "r_object_type"='dm_document' order by "r_modify_date" asc, "i_position" desc

But I need on开发者_运维百科ly first N of the objects the Select returns. I repeat: N objects, not rows (this matters because of the r_version_label in the resulting attributes is a repeatable field).

I tried to do this using the following DQL: select "r_version_label","i_chronicle_id", "i_position", "r_modify_date" , "subject","title","r_object_type","object_name","r_object_id" from "dm_document" where FOLDER (ID('0cde75d180000107')) and "r_object_type"='dm_document' order by "r_modify_date" asc, "i_position" desc ENABLE (OPTIMIZE_TOP , RETURN_TOP )

But I saw: the returned were rows, not objects. This is because my Documentum Server has the default parameter return_top_results_row_based (=true). Changing the parameter in the server.ini is not acceptable for me – I have to write an application that will work in the same manner whatever the return_top_results_row_based is.

I have tried RETURN_RANGE, SQL_DEF_RESULT_SET and FETCH_ALL_RESULTS instead of the RETURN_TOP – but their N is rows too.

So, now I see the only way to do this. I will use the following DQL: select "r_version_label","i_chronicle_id", "i_position", "r_modify_date" , "subject","title","r_object_type","object_name","r_object_id" from "dm_document" where FOLDER (ID('0cde75d180000107')) and "r_object_type"='dm_document' order by "r_modify_date" asc, "i_position" desc ENABLE (OPTIMIZE_TOP , RETURN_TOP )

And while processing the result my application will use only first N of the returned objects. Hope the “OPTIMIZE_TOP ” will minimize the time to read the objects I will not use. My DBMS is MSSQL, and the DQL Reference says the “OPTIMIZE_TOP ” does have effect for MSSQL.

Maybe someone can propose a better solution?

Try this query :

select "r_object_id, "r_version_label","i_chronicle_id", "i_position", "r_modify_date" , "subject","title","r_object_type","object_name","r_object_id" from "dm_document" where FOLDER (ID('0cde75d180000107')) and "r_object_type"='dm_document' order by "r_object_id", "r_modify_date" asc, "i_position" desc

Ordering on r_object_id should force dql to aggregrate rows into objects in the result collection. I don't really know how it interacts with OPTIMIZE/RETURN_TOP though.