DQL Query to find out Length of Object name

Srnu

Hello all,

I need help with below queries:

1.) Is there any way using DQL query that I can find the length of the object name in a cabinet?
2.) I know r_folder_path is a repeating attribute, but can I get the length of the folder path for a document object?

I have tried using
select len(object_name) from dm_document
select length(object_name) from dm_document
select char_length (object_name) from dm_document

but it is giving me error for all three ways.

Pedro Maia

I don't think there is a LENGTH function in DQL. You'll have to execute SQL from DQL like:
EXECUTE exec_sql WITH query='SQL Statement'

Hicham Bahi

You can't use EXEC_SQL to execute select statements. Alternative is to create a view in your database and register it as a registered table.

Pedro Maia

You are correct @bacham3, my fault, EXEC_SQL can't be used to execute SELECT statements. On the other hand, it could be used to populate an object's property with the length value required.

DCTM_Guru

The better question is what is the intent of the query - why are you concerned about length?

Srnu

SharePoint doesn't allow more than 260 characters including folder path + object name in the URL length, so before migration, I need to find all the objects (including name + path) that cross more than 260 characters in Documentum source

PanfilovAB

Though there is no built-in length function, you can take advantage of substr function to figure out whether your repository contains problematic objects or not, e.g. query like:

select r_object_id, object_name from dm_document where substr(object_name, 128, 1) is not nullstring

will return all objects with object_name's length exceeding 128 characters.

Srnu

Hi I tried the above query I am getting bellow error message

Error occured during query execution :[DM_QUERY_E_SYNTAX]error: "A Parser Error (syntax error) has occurred in the vicinity of: select r_object_id, object_name from dm_document where substr(object_name, 128, 1) is"

mpaube

Hi - I am also looking to find the length of folder path and document name for a future migration in SharePoint. I tried the DQL mentioned above and got the same error message. Does anyone have another idea?

Srnu

I didn't find the query for this but I used this manual approach as I was low on time. See if it helps

You can always export your DQL query result to excel and then find out the length using formula in excel =LEN(C1)

select d.r_object_id, d.object_name, f.r_folder_path
from
(select r_object_id, r_folder_path from dm_folder) f, (select r_object_id, object_name, i_folder_id from dm_document(all)
where FOLDER('/Temp', DESCEND)) d
where f.r_object_id = d.i_folder_id and f.r_folder_path is not null;

PS: If the documents are linked, you can get repetitions in this query result, you can sort using r_object_id

Wim K

All,

substring function suggested by PanfilovAB works, the syntax however was not quite correct.

I need to find user_names longer than 40 characters (max value for field is 255.

Here is the query, adopt this to solve your own problem...

select r_object_id, user_name, substr(user_name, 40, 1) from dm_user where substr(user_name, 40, 1) != ''

Good Luck ...