taggedValue not in Package, but in some Element?

[avanzwol]

I am maintaining some legacy C# code. I have Elements and Packages that where iterated through with COM objects to get the taggedValues. I noticed this is very slow.
So now I want to do it with queries and put the records in Dictionaries or Lists.

Let's say I have a Package with a Tag "A" with the value "Foo".

With a query

Code: [Select]

SELECT Object_ID, Property, Value FROM t_objectproperties I get all the Tags.

In my C# project I am iterating through the tree up to the root to get the Package with this taggedValue "A". But I noticed I cannot find it. A colleague told me that this Package has an Element which contains the Tag "A".

So now I use also the query

Code: [Select]

SELECT Object_ID AS Child_ID, Package_ID AS Parent_ID FROM t_object to iterate through the child Elements.

But when I find the Package I noticed it has three child Elements. If another child Element also has the Tag "A", things can get very messy. I don't want the Tag of some other sub-branch. So how do I know which Element I need?

[Geert Bellekens]

For each package (record in t_package) there is a single element (record in t_object)
Both records have the same ea_guid.

Tagged values are connected to elements (t_object) and not packages.

So to select all tagged values of your package you do

Code: [Select]

select * from t_objectproperties tv
inner join t_object o on o.Object_ID = tv.Object_ID
inner join t_package p on p.ea_guid = o.ea_guid
where p.ea_guid = '{17A598AA-0796-41ef-8435-6F6852F18ECE}'
and tv.Property = 'TagA'
Geert

[avanzwol]

@Geert Thanks, that makes it a lot clearer to me!

[avanzwol]

I'm not that experienced in queries, but now I would like to append the result of the query with the rest of the t_objectproperties records without getting double results.

This is what I have so far, but this is with double records:

Code: [Select]

select tv.Object_ID, tv.Property, tv.Value, p.ea_guid from t_objectproperties tv
inner join t_object o on o.Object_ID = tv.Object_ID
inner join t_package p on p.ea_guid = o.ea_guid
union select tv.Object_ID, tv.Property, tv.Value, '' from t_objectproperties tv

I tried outer join, but that keyword is not valid I think.

[Geert Bellekens]

I have no idea what you mean. What exactly are you looking for, and where are the "duplicates"?

union gets rid of any duplicates, so you must mean something else.

Geert

[avanzwol]

Let's say I have the following t_objectproperties:

Code: [Select]

Object_ID;Property;Value;
1;AAA;Foo;
1;BBB;Bar;
2;CCC;Zoo;

And let's say taggedValue "2;CCC;Zoo;" is a tag belonging to a package A. With your query I am able to get:

Code: [Select]

Object_ID;Property;Value;ea_guid;
2;CCC;Zoo;{guidPackageA};

But I would like to have

Code: [Select]

Object_ID;Property;Value;ea_guid;
1;AAA;Foo;;
1;BBB;Bar;;
2;CCC;Zoo;{guidPackageA};

(and with my query I get

Code: [Select]

Object_ID;Property;Value;ea_guid;
1;AAA;Foo;;
1;BBB;Bar;;
2;CCC;Zoo;;
2;CCC;Zoo;{guidPackageA};
).

How do I get the result without duplicate "2;CCC;Zoo;"?

[Geert Bellekens]

So you are saying, you want to see all tagged values that exist, but if they happen to be connected to a package, you want to see the package guid?
In that case you can simply make the inner join an outer join

Code: [Select]

select tv.Object_ID, tv.Property, tv.Value, p.ea_guid
from t_objectproperties tv
inner join t_object o on o.Object_ID = tv.Object_ID
left join t_package p on p.ea_guid = o.ea_guid
Geert

[avanzwol]

That's is what I tried. But I do get an error:

Incorrect syntax near the keyword 'join'.  So the keyword 'outer' is not a valid keyword?

[Geert Bellekens]

Sorry, must me "left" instead of "outer". I don't know where my head was.

Geert

[avanzwol]

Thanks! That's it!