Sparx Systems Forum

Discussion => General Board => Topic started by: Ravaa on November 07, 2017, 02:47:24 am

Title: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on November 07, 2017, 02:47:24 am
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 (http://"https://bellekens.com/2011/01/14/harvesting-the-power-of-eas-sql-searches")', 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#'

Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Geert Bellekens on November 07, 2017, 06:11:53 am
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
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on November 07, 2017, 08:36:59 pm
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
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Geert Bellekens on November 07, 2017, 08:55:52 pm
Use left join instead of inner join.

Geert
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on November 09, 2017, 04:19:16 am
Thanks Geert! Much appreciated
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on November 10, 2017, 08:18:22 pm
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?
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Geert Bellekens on November 10, 2017, 08:52:57 pm
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
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on November 16, 2017, 09:31:53 pm
Thanks Geert, and apologies for late response. I'll work with this and let you know if I have any challenges!
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Richard Freggi on November 17, 2017, 12:22:57 am
Thanks you Geert this is wonderful, I'll try this during Xmas holidays!
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on December 01, 2017, 10:50:55 pm
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?
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Geert Bellekens on December 01, 2017, 11:27:35 pm
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
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: qwerty on December 02, 2017, 05:20:00 am
Are you dealing with EAP? In that case the asterisk is fine. But other RDBMS use a percent sign.

q.
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: Ravaa on December 05, 2017, 07:35:15 pm
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:
Quote
DAO.QueryDef [3135]
Syntax error in JOIN operation.

What do you think?

Really appreciate the support so far!
Title: Re: SQL query to get class, description, attribute & tagged values
Post by: qwerty on December 05, 2017, 08:38:20 pm
Look into %appdata%/..sparx.../dberror.txt. That might have more info.

q.