EMC Documentum DQL - How to delete repeating attribute

Question

I have a few objects created on my database and I need to delete some of the repeating attributes related to them. The query I'm trying to run is:

UPDATE gemp1_product objects REMOVE ingredients[1] WHERE (r_object_id = '08015abd8002cd68')

But all I get is the folloing error message:

Error querying databse. [DM_QUERY_E_UPDATE_INDEX]error: "UPDATE: Unable to REMOVE tghe attribute ingredients at index 1." [DM_OBJECT_W_DELETE_ATTR_POSITION_ERROR]warning: "attempt to delete non-existent attribute 88"

Object 08015abd8002cd68 exists and I can see it on the database. Queries like SELECT and DELETE work fine but I do not want to delete the whole object.

Answer 1

There is no easy way to do this. The reason is that repeating attributes are ordered, to enable multiple repeating attributes to be synchronized for a given object.

Either

1. set the attribute value to be empty for the given position, and change your code to discard empty attributes, or
2. use multiple DQL statements to shuffle the order so that the last one becomes empty, or
3. change your data model, e.g. use a single attribute as a property bag with pre-defined delimiters.

Details (1)

UPDATE gemp1_product OBJECTS SET ingredients[1] = '' WHERE ...

Details (2)

For each index; first find the value of index+1:

SELECT ingredients
FROM gemp1_product
WHERE (i_position*-1)-1 = <index+1>
ENABLE (ROW_BASED)

Use the value in a new query:

UPDATE gemp1_product OBJECTS SET ingredients[1] = '<value_from_above>' WHERE ...

It should also be possible to do this by nesting DQL somehow, but it might not be worth the effort.

Answer 2

Something is either wrong with your query or with your repository. I think you are mistyping your attribute name or using wrong index in your UPDATE query. If you google for DM_OBJECT_W_DELETE_ATTR_POSITION_ERROR you'll see on this link a bit more detailed explanation:

CAUSE: Program executed a DeleteAttr operation that specified an non-existent attribute position (either a negative number or a number larger than the number of attributes in the object).

From this you could guess that type isn't in consistent state, or that you are trying to remove too big index of your repeating attribute, etc. Did you checked your repository with Consistency checker Job and other similar Jobs?

As of for the removing of repeating property (sttribute) value with DQL query, this is unachievable with single query since you need to specify index position which you don't know at first. But writing a simple script or doing it manually if it's not big amount of values to delete is the way you want to go.