RETURN_TOP and repeating properties

michael_bailey

Hello everyone!

I have a DQL query that is using the RETURN_TOP hint and whenever repeating properties are included, the hint acts as if it was the ROW_BASED hint.

For example, this query...

select r_object_id, i_chronicle_id from dm_document where r_object_id = '09f42cac80006485' enable (return_top 20)

...returns a single row (as you might expect). However, this query...

select r_object_id, i_chronicle_id, i_folder_id from dm_document where r_object_id = '09f42cac80006485' enable (return_top 20)

...returns 4 rows. The same behaviour is observed whenever one or more repeating properties are added to the query. However, if I use FETCH_ALL_RESULTS (on a Oracle 11g database), it returns a single row, as you'd might expect.

Reading the DQL Reference Manual for Content Server 6.7, I can't see why RETURN_TOP would act in the this manner. I've tried this on a 6.7 and 6.5 Content Server and the same results occur as above. Am I doing anything wrong here?

michael_bailey

Apologies, I think I mis-read the manual...

Since an object with repeating properties may consist of multiple rows, this hint may return fewer than N objects.

In release 6.5 SP2, a server.ini flag was added, return_top_results_row_based, true by default, that controls whether RETURN_TOP returns the number of rows or the number of objects specified.Setting this flag to false (return_top_results_row_based = F), causes the returned results to be limited by the number of objects.

That would explain the odd behaviour.