Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: MarcinG on February 01, 2022, 08:32:04 pm
-
Hi,
I have some elements, for which I defined tagged values. There might be : Element {Type=MyType, AnotherTag=MyTag} and Element2 {Type=MyType, AnotherTag=DifferentTag}.
Is there any simple way to either build a query, or use SQL query (without pivots etc), that would allow finding Element + AnotherTag, using Type as filter (or part of where clause) ?
Best regards
Marcin
-
You have to JOIN t_object.object_id with the corresponding t_objectproperties.
q.
-
Thank you. I know I need to join those two. But as it is 1:M , filtering by any value from t_objectproperties will automatically eliminate any other record which contains values I need.
Marcin
-
Thank you. I know I need to join those two. But as it is 1:M , filtering by any value from t_objectproperties will automatically eliminate any other record which contains values I need.
Marcin
You can do a left join. This will show the result of the tagged value if they exist, but also show the element if the tagged value doesn't exist.
Make sure you add all constraints to t_objectproperties in the join conditions and not in the where clause, otherwise you'll effectively make it an inner join again.
select o.name, tv.Value as tagValue
from t_object o
left join t_objectproperties tv on tv.Object_ID = o.Object_ID
and tv.Property = 'Tagname'
Geert
-
Thank you Geert,
I think I must have been not very clear in my description ;-). I did it like this :
SELECT distinct bi_object.ea_guid , bi_object.Object_ID, bi_object.Name, bi_object.Alias, t_objectproperties.Value
from
(
SELECT distinct t_object.ea_guid , t_object.Object_ID, t_object.Name, t_object.Alias, t_objectproperties.Value
from t_object,t_objectproperties
WHERE
t_objectproperties.Object_ID = t_object.Object_ID
and t_objectproperties.Value = 'MyType') as bi_object
join t_objectproperties on t_objectproperties.Object_ID = bi_object.Object_ID
WHERE t_objectproperties.Property = 'MyProperty'
it seems to be doing exactly what I need it to do :-)
Marcin
-
It feels like you are making it way more complicated then needs to be.
select o.ea_guid, o.Object_ID, o.Name, o.Alias, tvmt.Value as MytypeValue, tvmp.Value as MyPropertyValue
from t_object o
left join t_objectproperties tvmt on tvmt.Object_ID = o.Object_ID
and tvmt.Value = 'MyType'
left join t_objectproperties tvmp on tvmp.Object_ID = o.Object_ID
and tvmp.Property = 'MyProperty'
Weird thing about your query is that you are testing that the value of the tagged value is 'MyType' rather than the Property, but maybe that is a typo?
t_objectproperties.Value = 'MyType'
Geert
-
No, that's not a typo - I am checking two different tags. For one tag - all my objects for particular project will have the same value, so I am checking the value. But then, for those objects, I need to get a value of another tag (property).
-
No, that's not a typo - I am checking two different tags. For one tag - all my objects for particular project will have the same value, so I am checking the value. But then, for those objects, I need to get a value of another tag (property).
So for the one tag you don't care about the name, but only about the value?
If you are only interested in the value of the tagged value MyProperty in case there is a tagged value with with MyType, then you can add that to the join criteria
select o.ea_guid, o.Object_ID, o.Name, o.Alias, tvmp.Value as MyProperty
from t_object o
left join t_objectproperties tvmp on tvmp.Object_ID = o.Object_ID
and tvmp.Property = 'MyProperty'
and exists (select tvmt.ea_guid from t_objectproperties tvmt
where tvmt.Object_ID = o.Object_ID
and tvmt.Value = 'MyType')
Geert