Query on Dql queries

Ram5160

Can anyone guide how to use RETURN_PAGE option...I tried below but not works. I need to fetch 10 to 100 th row alone.

Select r_object_id, object_name from dm_folder where folder('') ENABLE( RETURN_PAGE 10 100 'r_object_id ASC');

DCTM_Guru

Are you trying to find folders that are not linked? If not, remove the folder() predicate in your search criterion:

Select r_object_id, object_name from dm_folder enable(RETURN_RANGE 50 100 'r_object_id ASC')

Also, its bad forum etiquette to repost the same question.

Ram5160

Thanks for reply..i tried it, but returns error saying A parser Error has occurred in vicinity.Executed query is same as you preferred.

Full Error message as [DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: Select r_object_id, object_name from dm_folder enable(RETURN_RANGE 50 100 'r_object_id ASC'"

DCTM_Guru

Did you include closing parenthesis ")"

Ram5160

Hi,
I have provided it. But in error it's now showing closing paranthesis.

DCTM_Guru

Where are you running your DQL? You can validate your DQL syntax by running it in DA. If you are doing this programmatically, then you have to make sure that you are escaping special chars appropriately.

Ram5160

Hi ,
I am Documentum 6.7 Samson tool for running Dql query. Whether any version disturbency is there..?

DCTM_Guru

If you are running in Samson, then you should be able to run this in Documentum Administrator as well.

There are other utilities (e.g. dqMan, RePoint, etc) that works with CS 6.7. However, these utilities do not seem to work with newer 16.4 version, since dmcl is no longer supported.

Ram5160

Actual need for me is to retrieve the records based on Rownum....if you have any suggestions for it..Thanks in advance

DCTM_Guru

Not sure what you are trying to do. If you are NOT doing this programmatically, why do you need to retrieve records based on Rownum? Why cant you just retrieve all of them, export them to xls file, and the retrieve the specific records you want based on row numbers.

Ram5160

I cannot able to retrieve particular records by mentioning Rownum . I am trying it from Java coding level.

DCTM_Guru

Definitely easier to do this programmatically.

Ram5160

Please suggest some queries for it ,other than return_range

DCTM_Guru

What are you talking about? If you do this programmatically, it works as expected. You are getting this error in Samson, but the query works fine in Documentum Administrator, so there is nothing wrong with the query.

fmejwagner

I think the main problem is: attr/column "rownum" not availabe at DQL level...

AFAIK that is a dynamically added attr/column in the underlying RDBMS (Oracle?).

To have it available at DQL level you may do this:
1. Transform your intended DQL query to SQL adding that internal rownum attr/col as a result attr/column.
2. Define a view in your underlying RDBMS representing your query.
3. Register that view in Documentum (registered table)
4. Query the Documentum registered table applying filter criteria to now available attr/column "rownum"

Hint:
You may not use name "rownum" but some other alias because that may be a reserved word?!

DCTM_Guru

I guess my question is why does OP need to use rownum. Using r_object_id is better way to process objects, since you know it is a unique num. If the concern is track which objects has been processed, then the best way to do log processed object IDs and/or errors.

fmejwagner

I checked DQL documentation...

Hint "ENABLE(RETURN_RANGE...)" was not available in 6 and 6.5.

Finally in 6.7 DQL documentation this feature appears... maybe his Samson tool or his Content Server uses some too old ancient DFC ?

DCTM_Guru

The OP said he's on CS 6.7. He said he was going to try this using DFC, so the query should work if he's using at least DFC 6.7.

Ram5160

Thanks for your replies..I have Documentum version 6.5 which may not support Return_Range query.

DCTM_Guru

You have your answer then. As I said in previous post, if you are doing this programmatically, you can loop thru the whole result set and process them however you seem fit.