Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: M1TO on March 17, 2025, 07:42:10 pm
-
I have trouble writing the correct SQL query for the following problem: I want to find all elements of a specific type which don't have a specific child element but can have other child elements.
My approach was:
SELECT DISTINCT obj.ea_guid AS CLASSGUID, obj.Object_Type AS CLASSTYPE, obj.Name
FROM t_object obj
WHERE NOT EXISTS (
SELECT * FROM t_object obj2 WHERE obj2.ParentID = obj.object_id AND obj2.Object_Type = 'SpecificType')
AND obj.Stereotype = 'Some Stereotype'
I am pretty sure, the solution is simple. So what am I missing?
-
And the problem is?
Geert
-
The problem is, that the query does not find the elements with the missing child element. It lists all the elements, regardless whether the child element is existing or not.
-
The query seems correct.
Maybe you should first figure out how to find the elements that have such a child element.
Select * from t_object o where o.Object_Type = 'SpecificType'Should give you a clue to figure out what to look for.
Geert
PS. I remember a weird behavior in one of the version of EA that an empty line was interpreted as the end of the query. The rest was simply not processed. Try removing the empty lines to see if that helps.
-
The query seems correct.
Maybe you should first figure out how to find the elements that have such a child element.
That is not a problem and works just fine.
PS. I remember a weird behavior in one of the version of EA that an empty line was interpreted as the end of the query. The rest was simply not processed. Try removing the empty lines to see if that helps.
That sounds really weird. :o Tried it, but doesn't change anything.
-
The query seems correct.
Maybe you should first figure out how to find the elements that have such a child element.
That is not a problem and works just fine.
So you mean that your query with "WHERE EXISTS" works perfectly (get you the elements), but "WHERE NOT EXISTS" also includes the same elements?
That seems hard to believe. There must be something else playing. An element can't be on both datasets of they have opposit criteria.
Geert
-
I had the query with the existing elements a little bit different and that worked, yes. But I found the reason: The Object_Type of the element is different to the one in the Properties Window. Now I use the Stereotype instead, and it works. :)
Sometimes "talking" about the problem helps. Thanks Geert.
-
I had the query with the existing elements a little bit different and that worked, yes. But I found the reason: The Object_Type of the element is different to the one in the Properties Window. Now I use the Stereotype instead, and it works. :)
Sometimes "talking" about the problem helps. Thanks Geert.
Yes, the properties show the "metatype" as type, not the actual value in Object_Type
Using the stereotype is in most cases the better option.
Geert