DQL Query to find out Length of Object name
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.
Comments
-
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'Pedro Maia
Senior Consultant
OpenText0 -
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.
1 -
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.
Pedro Maia
Senior Consultant
OpenText0 -
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.
0 -
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
0 -
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 ...
0
Categories
- All Categories
- 108 Developer Announcements
- 53 Articles
- 106 General Questions
- 145 IM Services
- 43 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 899 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 120 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management