SQL query to get class, description, attribute & tagged values

[Ravaa]

Hi folks

I'm currently attempting to obtain a search that provides me the following information:

- Class
- Class description
- Attribute (so 'Class' would repeat for multiple attributes per class)
- Attribute tagged values (so 'Attribute' and 'Class' would repeat for multiple tagged values per attribute)

I've had a look at Geert's 'favourite SQL queries', and figured the SQL query below is probably the closest I can get to the above - however my SQL knowledge is almost non-existent so not sure how to expand to include the above. Can someone please assist?

Code: [Select]

select a.ea_guid as CLASSGUID,'Attribute' as CLASSTYPE,a.name as Name, a.Type as 'Type',  class.name as 'Class Name'
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_attribute  a
inner join t_object class on a.object_id = class.object_id)
inner join t_package package on class.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where a.Name like '#WC#<Search Term>#WC#'

[Geert Bellekens]

Try this.

Code: [Select]

select a.ea_guid as CLASSGUID,'Attribute' as CLASSTYPE,class.name as Class, class.Note as [Class Description], a.name as Attribute, a.Type,
tv.Property as TagName, tv.VALUE as TagValue,
package.name as [Package Name] ,package_p1.name as [Package level -1],package_p2.name as [Package level -2],package_p3.name as [Package level -3]
from ((((((t_attribute  a
inner join t_object class on a.object_id = class.object_id)
inner join t_attributetag tv on tv.ElementID = a.ID)
inner join t_package package on class.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where a.Name like '#WC#<Search Term>#WC#'You only need to join t_attributetag to get to the tagged values.

Geert

[Ravaa]

That's brill, thanks Geert! Does the trick - I use '*' as a search term to show everything as my diagram is fairly small

How about if I have attributes which don't have any tags at all, but I still want to show them in the search results (just with blank tags)?

At the moment they don't show up in the search results

[Geert Bellekens]

Use left join instead of inner join.

Geert

[Ravaa]

Thanks Geert! Much appreciated

[Ravaa]

Just following up on this, as I wonder if I'm using tagged values appropriately...

My model has the same TagName in all the class elements. I'm wondering if it's possible to modify the above SQL query to have the TagNames return as column heads, with the TagValues returning as values for those column heads for each attribute. Attributes are unique for each class

For example, if class1 has attributes att1, att2...att1 has TagNames tag1 and tag2, as does att2. I want to return tag1 and tag2 as column headers instead of just 'TagName', and have the TagValues for these TagNames return as values for these columns

If not possible, is there something else to capture these common attribute properties instead of tagged values for each of the attributes?

[Geert Bellekens]

Yes, that is possible.
Join t_attribute tags multiple times, each time with a constraint on the tag name.
Something similar to this:

Code: [Select]

left outer join [t_attributetag] v on (v.[ElementID] = att.[ID]
                                and v.[Property] = 'Versioned'))
left outer join [t_attributetag] ts on (ts.[ElementID] = att.[ID]
                                and ts.[Property] = 'Timesliced'))
left outer join [t_attributetag] dc on (dc.[ElementID] = att.[ID]
                                and dc.[Property] = 'Data Classification'))
This gets the data for tagged values with name Versioned, Timesliced and Data Classification.
Use dc.Value as DataClassification in order to use the field in your result set.

Geert

[Ravaa]

Thanks Geert, and apologies for late response. I'll work with this and let you know if I have any challenges!

[Richard Freggi]

Thanks you Geert this is wonderful, I'll try this during Xmas holidays!

[Ravaa]

Just had a chance to play around with this some more, so to clarify...code would look something like this?

Code: [Select]


select a.ea_guid as CLASSGUID,'Attribute' as CLASSTYPE,class.name as Class, class.Note as [Class Description], a.name as Attribute, a.Type,
tv.Property as TagName, tv.VALUE as TagValue,
package.name as [Package Name] ,package_p1.name as [Package level -1],package_p2.name as [Package level -2],package_p3.name as [Package level -3]
from ((((((t_attribute  a
left join t_object class on a.object_id = class.object_id)

left outer join [t_attributetag] v on (v.[ElementID] = att.[ID]
and v.[Property] = 'Versioned')
left outer join [t_attributetag] ts on (ts.[ElementID] = att.[ID]
and ts.[Property] = 'Timesliced')
left outer join [t_attributetag] dc on (dc.[ElementID] = att.[ID]
and dc.[Property] = dc.Value)

left join t_package package on class.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)

where a.Name like '*'

Doesn't seem to return anything so I've clearly messed something up - can you please clarify what I'll need to tweak?

[Geert Bellekens]

What happens if you leave out the where clause?

I don't see any inner joins, so it must be the where clause.

If you are using it as a search in EA then you can replace the '*' by '#WC#<Search Term>#WC#'

#WC# will be replaced by the wildcard appropriate for your database

Geert

[qwerty]

Are you dealing with EAP? In that case the asterisk is fine. But other RDBMS use a percent sign.

q.

[Ravaa]

Thanks guys, seems the 'where' and '*' were fine, however with a little tweak I've at least managed to get it to produce an error message.

I now get the following error:

DAO.QueryDef [3135]
Syntax error in JOIN operation.

What do you think?

Really appreciate the support so far!

[qwerty]

Look into %appdata%/..sparx.../dberror.txt. That might have more info.

q.

[Richard Freggi]

Hello, I'm reviving this old thread because I think it's great but I have a question I could not answer by googling around: how to show the full content of the complex tagged values (I use memo type along with some simple tagged values) in the query result?  If I follow this thread the query only returns the tag names and then just the text <memo> instead of the content of the tagged value.  Thanks!!!