SQL for Diagram List extended with TaggedValues

[Daiim]

Hey,

I'd like to list diagram objects (only elements) and extend the default with inherited TaggedValues. So some Elements will have a value for it, some may not. From examples I created the following query:

Code: [Select]

SELECT
   item.Name AS Name,
   a.Value AS 'eMail',
   b.Value AS 'Mobile',
   c.Value AS 'Telephone,
   d.Value AS 'Room'
FROM ((((t_object item
LEFT OUTER JOIN t_objectproperties AS a ON (item.Object_ID = a.Object_ID AND a.Property = 'e-Mail'))
LEFT OUTER JOIN t_objectproperties AS b ON (item.Object_ID = b.Object_ID AND b.Property = 'Phone'))
LEFT OUTER JOIN t_objectproperties AS c ON (item.Object_ID = c.Object_ID AND c.Property = 'Mobile'))
LEFT OUTER JOIN t_objectproperties AS d ON (item.Object_ID = d.Object_ID AND d.Property = 'Room'))
WHERE item.Object_Type = 'Actor' AND NOT item.Abstract
But it only shows elements that have all TaggedValues set to an value. I need to get these Columns for every element, no matter if it has this TaggedValue set or not. (With 'not set' I mean that it is only inherited but not applied to the specific element.)
Can anyone give me a hint please?

Thank you guys!

[Geert Bellekens]

If by "inherited" you mean the element is a specialization of another "parent" element, and that parent element has a value for this tagged value.

In that case you need to join t_connector with connector_Type = 'Generalization' and then to t_object again to find the parent object.
And then check the parent object's tagged values.

Geert

[Daiim]

I just want to specify in code (see example) which tagged values might be interesting, but i need the possibilities to be a separate row in the list (NULL, empty, or what ever, if the t_objectproperty does not contain such a taggedvalue for the element).

Expected output:

#	Name	eMail	Mobile	Telephone	Room
0	Bond, James	[email protected]			R.007
1	X, Malcom	[email protected]	0991 1214253523	+2 2342 23523523

[Geert Bellekens]

I just want to specify in code (see example) which tagged values might be interesting, but i need the possibilities to be a separate row in the list (NULL, empty, or what ever, if the t_objectproperty does not contain such a taggedvalue for the element).

Expected output:

#	Name	eMail	Mobile	Telephone	Room
0	Bond, James	[email protected]			R.007
1	X, Malcom	[email protected]	0991 1214253523	+2 2342 23523523

I don't understand what you mean by a separate row. Which row is separate?

Geert

[Daiim]

In the model, the TaggedValue: "eMail", "Mobile", "Telephone" and "Room" are different Tags. So each Tag should have a separate columm (NOT row, sorry).

[qwerty]

The problem with inner join is that all need to have values in order to create a row. Try with a simple where instead. Not sure if that will really help (not a SQL expert here).

q.

[Geert Bellekens]

Your query already selects the different tags in different columns.

I don't understand the problem you are having.

Geert

(You are using the left joins correctly, don't put any tag related things in the where clause because that will turn your left joins into inner joins.)

[Daiim]

The problem with inner join is that all need to have values in order to create a row. Try with a simple where instead. Not sure if that will really help (not a SQL expert here).

Yes, so far - I think - I understood the problem. I could not express it neither, that's why I ask. Additionally EA seems to have some differences in SQL statement syntax. :/

[Daiim]

Your query already selects the different tags in different columns.

I don't understand the problem you are having.

Geert

(You are using the left joins correctly, don't put any tag related things in the where clause because that will turn your left joins into inner joins.)

The query only works for elements, where an element from t_object has a row for ALL 4 TaggedValues in t_objectproperties. I try to get it working for elements with 0..4 relations in the t_objectproperties table, too. "Missing" links should be represented with 'null' in the associated list column.

[qwerty]

I guess you need to use 4 single queries and join the result outside. I tried running with a WHERE and that simply broke EA/Mickeysoft's Access.

q.

[Geert Bellekens]

this

Code: [Select]

SELECT
   item.Name AS Name,
   a.Value AS eMail,
   b.Value AS Mobile,
   c.Value AS Telephone,
   d.Value AS Room
FROM ((((t_object item
LEFT OUTER JOIN t_objectproperties AS a ON (item.Object_ID = a.Object_ID AND a.Property = 'e-Mail'))
LEFT OUTER JOIN t_objectproperties AS b ON (item.Object_ID = b.Object_ID AND b.Property = 'Phone'))
LEFT OUTER JOIN t_objectproperties AS c ON (item.Object_ID = c.Object_ID AND c.Property = 'Mobile'))
LEFT OUTER JOIN t_objectproperties AS d ON (item.Object_ID = d.Object_ID AND d.Property = 'Room'))
WHERE item.Object_Type = 'Actor' AND NOT item.Abstract = 1
Returns a whole lot of results on my model, all with NULL for the tagged value columns.
I had to fix some syntax errors to get it to work at all. I tested this on SQL Server.

Geert

[Daiim]

Sorry for the syntax error. I had to reconstruct it from memory as I had no access to my machine.

Your query provokes an DAO.QueryDef [0x00000c45] error: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect". I use it on Chart Details Source in EA with an EAP file, tested on Version 15. It works well on EA 14 with an eap file, but only for elements that have all 4 TaggedValues in the t_objectproperties table. :/ 

I've no idea how to solve that.

[Geert Bellekens]

Yeah, MS-Access syntax can sometimes be tricky.

This one works on a .eap file

Code: [Select]

SELECT
o.name,
a.Value as [email],
b.Value as [Phone],
c.value as [Mobile],
d.value as [Room]
FROM ((((t_object o
left join t_objectproperties a on (a.Object_ID = o.Object_ID and a.Property = 'e-Mail'))
left join t_objectproperties b on (b.Object_ID = o.Object_ID and b.Property = 'Phone'))
left join t_objectproperties c on (c.Object_ID = o.Object_ID and c.Property = 'Mobile'))
left join t_objectproperties d on (d.Object_ID = o.Object_ID and d.Property = 'Room'))
WHERE o.Object_Type = 'Actor'
AND NOT o.Abstract
Geert

[Daiim]

Your query works on EA14 with an EAP file, true - but again - only if there is an table entry for all four TaggedValues.I need a result if there are 0..4 entries.

[qwerty]

Geert, the problem is if you have elements that only have 3 and not all 4 tags present (not with null value but missing!). They will not be in the result set.

q.