“Which are the repeating attribures in my_document?” That question was put by Gaurav as he needed to validate them with Captiva. He was finding it too tedious to validate the attributes by checking the definition of each type in the repository. He was interested in a single DQL query which could make his job easy.
My first reaction to his query was: “You are expecting too much!”. Life can’t be that easy. You can not escape from every complexity. But we can indeed try to make them simple. Can’t we?
So I looked into dmi_dd_type_info, dmi_dd_attr_info and finally in dm_type I found the attributes that I could use. After a few iterations I came up with the following DQL Query:
SELECT name, attr_name, attr_repeating FROM dm_type WHERE name = 'my_document' AND attr_repeating = 1 ENABLE(ROW_BASED)
This query is again a good example of the DQL Hint ROW_BASED. With this Hint we get only those results where attr_repeating = 1. That statement may have sounded dumb had attr_repeating been a single valued attribute. But it is repeating and there are some weirdness with the result if we are checking for the value of a repeating attribute in the WHERE clause without using that hint.
SELECT name, attr_name, attr_repeating FROM dm_type WHERE name = 'my_document' AND ANY attr_repeating = 1
The result of the first query brought a smile to Gaurav’s face; but his expectations had increased. He was interested in only those repeating attributes that were not inherited from the parent type. We found a simple way to do that.
SELECT name, attr_name, attr_repeating FROM dm_type WHERE name = 'my_document' AND attr_repeating = 1 AND attr_name NOT IN (SELECT attr_name FROM dm_type WHERE name = 'dm_document') ENABLE(ROW_BASED)
We came up with one more modification and our final query looked like:
SELECT name, attr_name, attr_repeating FROM dm_type WHERE name = 'my_document' AND attr_repeating = 1 AND attr_name NOT IN (SELECT attr_name FROM dm_type WHERE name = (SELECT super_name FROM dm_type WHERE name = 'my_document') ) ENABLE(ROW_BASED)
Now that has brought a smile to my face and I hope that Gaurav doesn’t come up with more expectations.
Leave a comment