Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: Mauricio Moya (Arquesoft) on April 13, 2018, 05:50:34 am
-
We are implementing a custom search in SQL (for a model stored in SQLServer) trying to get a list of elements and their optional 'n' tagged values.
In our scenary, there are some elements without tagged values, other with some (< 'n') tagged values and other with all the 'n' tagged values.
We are trying a query structured like that:
SELECT o.ea_guid AS CLASSGUID,
o.Object_Type AS CLASSTYPE,
o.Name,
tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
...
tvn.Value AS tvn
FROM t_object o
LEFT JOIN t_objectproperties tv1 ON tv1.Object_ID = o.Object_ID
LEFT JOIN t_objectproperties tv2 ON tv2.Object_ID = o.Object_ID
LEFT JOIN t_objectproperties tv3 ON tv3.Object_ID = o.Object_ID
...
LEFT JOIN t_objectproperties tvn ON tvn.Object_ID = o.Object_ID
WHERE tv1.Property = 'TVName1'
AND tv2.Property = 'TVName2'
AND tv3.Property = 'TVName3'
...
AND tvn.Property = 'TVNameN'
But it does not returns the expected results (all the elements and the values of the tags if they are present).
Any idea in how to structure this query? What's wrong?
-
Aliases are wrong?
tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
...
should be...
tv1.Value AS tv1,
tv2.Value AS tv2,
tv3.Value AS tv3,
...
-
Not sure whats returned, but this may also be an option,
SELECT t_object.ea_guid AS CLASSGUID,
t_object.Object_Type AS CLASSTYPE,
t_object.Name,
t_object.Stereotype,
t_object.Author,
t_objectproperties.Value As TAGVALUE
FROM t_object, t_objectproperties
WHERE t_object.Object_ID = t_objectProperties.Object_ID and t_objectproperties.Property in ('TV Name1', 'TV Name2')
-
Aliases are wrong?
tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
...
should be...
tv1.Value AS tv1,
tv2.Value AS tv2,
tv3.Value AS tv3,
...
Sure, just a typo when copy-pasting and editing.
-
Not sure whats returned, but this may also be an option,
SELECT t_object.ea_guid AS CLASSGUID,
t_object.Object_Type AS CLASSTYPE,
t_object.Name,
t_object.Stereotype,
t_object.Author,
t_objectproperties.Value As TAGVALUE
FROM t_object, t_objectproperties
WHERE t_object.Object_ID = t_objectProperties.Object_ID and t_objectproperties.Property in ('TV Name1', 'TV Name2')
The problem with this option is that there is only one column (in the results) with the value of all tag values and for each element you have multiple rows (one for each tag value).
The idea is to have a single row per element and the tag values as columns, even with bull values if they don't exist.
-
The problem is that your where clause makes the left joins inner joins again by checking on the tv.Property.
So whenever a tagged is existent, the whole record won't show.
If you include that statement in "ON" statement you won't have that problem.
For SQL server you can do something like this:
SELECT o.ea_guid AS CLASSGUID,
o.Object_Type AS CLASSTYPE,
o.Name,
tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
...
tvn.Value AS tvn
FROM t_object o
LEFT JOIN t_objectproperties tv1 ON tv1.Object_ID = o.Object_ID
AND tv1.Property = 'TVName1'
LEFT JOIN t_objectproperties tv2 ON tv2.Object_ID = o.Object_ID
AND tv2.Property = 'TVName2'
LEFT JOIN t_objectproperties tv3 ON tv3.Object_ID = o.Object_ID
AND tv3.Property = 'TVName3'
..
LEFT JOIN t_objectproperties tvn ON tvn.Object_ID = o.Object_ID
AND tvn.Property = 'TVNameN'
For MS Access syntax you'll have to add parenthesis
SELECT o.ea_guid AS CLASSGUID,
o.Object_Type AS CLASSTYPE,
o.Name,
tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
...
tvn.Value AS tvn
FROM ((((t_object o
LEFT JOIN t_objectproperties tv1 ON (tv1.Object_ID = o.Object_ID
AND tv1.Property = 'TVName1'))
LEFT JOIN t_objectproperties tv2 ON (tv2.Object_ID = o.Object_ID
AND tv2.Property = 'TVName2'))
LEFT JOIN t_objectproperties tv3 ON (tv3.Object_ID = o.Object_ID
AND tv3.Property = 'TVName3'))
..
LEFT JOIN t_objectproperties tvn ON (tvn.Object_ID = o.Object_ID
AND tvn.Property = 'TVNameN'))
Geert
-
Thanks Geert, it worked!