Urgent - Convert DQL to SQL
We have a custom type that contains a field company_department. Due to department restructuring, it is necessary to change the company_department drop-down (done via Composer) and the current company_department values. I would like to run the update with SQL Oracle since there are millions of changes.
I have logged into DA and ran the select statement and converted the DQL to 'Show in SQL' - same results (great).
When I run the following update statement in DQL...
update custom_type object
set company_department = 'Membership and Contact Centre Services'
where company_department = 'Contact Centre'
the SQL (in attachment) is completely different...
How do I convert the DQL to SQL to have it run in Oracle SQL.
Thank you
Best Answer
-
It will update all versions.
To update only the current ones, you should add to the where clause :
and r_object_id in (select r_object_id from dm_sysobject_s where i_has_folder = 1 and r_object_type = 'custom_type')
NB : the part r_object_type = 'custom_type' is unneccessary, it's just here to limit the number of rows of the in clause ; be careful though, if you add this clause, it will only update the objects of the type custom_type and not the extended types (if any).
0
Answers
-
The most easist way is to generate an Update SQL query using DA, like you did with the select query.
Since you dont want to execute your update query for all the millions records, limit your update query with:
update custom_type object
set company_department = 'Membership and Contact Centre Services'
where company_department = 'Contact Centre' and r_object_id = '<some object id>'
In the generated SQL query you only have to remove the last condition : r_object_id = '<some object id>'
0 -
When I executed the DQL - 'Update' statement and checked the SQL box, the returned query was a SQL '[Select' statement and not a SQL Update one.
I think that this is the functionality of converting DQL to SQL from DA, the SQL results are the count of updated records and not the actual 'Update' statement.
My quest continues......
0 -
every custom object is represented in DB in two tables (and views): object_type_s (single attributes) and object_type_r (repeating attributes). If you don't need to use inherited types you can update those tables directly (ie: update obj_type_s set custom_att='new value' where custom_att='old_value'), otherwise you'll need to join with dm_sysobject_s/r and/or any other supertype.
Just make sure you test changes first as this is a not supported/recommended/etc way to do an update
0 -
It will update all versions.
To update only the current ones, you should add to the where clause :
and r_object_id in (select r_object_id from dm_sysobject_s where i_has_folder = 1 and r_object_type = 'custom_type')
NB : the part r_object_type = 'custom_type' is unneccessary, it's just here to limit the number of rows of the in clause ; be careful though, if you add this clause, it will only update the objects of the type custom_type and not the extended types (if any).
0
Categories
- All Categories
- 111 Developer Announcements
- 49 Articles
- 102 General Questions
- 123 IM Services
- 40 OpenText Hackathon
- 31 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 898 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 124 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management
- 1 XM Fax