Author Topic: SQL query to get class, description, attribute & tagged values  (Read 450 times)

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
SQL query to get class, description, attribute & tagged values
« 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', 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

  • EA Guru
  • *****
  • Posts: 7731
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query to get class, description, attribute & tagged values
« Reply #1 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

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #2 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

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7731
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query to get class, description, attribute & tagged values
« Reply #3 on: November 07, 2017, 08:55:52 pm »
Use left join instead of inner join.

Geert

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #4 on: November 09, 2017, 04:19:16 am »
Thanks Geert! Much appreciated

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #5 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?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7731
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query to get class, description, attribute & tagged values
« Reply #6 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

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #7 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!

Richard Freggi

  • EA User
  • **
  • Posts: 42
  • Karma: +1/-1
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #8 on: November 17, 2017, 12:22:57 am »
Thanks you Geert this is wonderful, I'll try this during Xmas holidays!

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #9 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?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7731
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query to get class, description, attribute & tagged values
« Reply #10 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

qwerty

  • EA Guru
  • *****
  • Posts: 8958
  • Karma: +136/-123
  • I'm no guru at all
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #11 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.

Ravaa

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #12 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!

qwerty

  • EA Guru
  • *****
  • Posts: 8958
  • Karma: +136/-123
  • I'm no guru at all
    • View Profile
Re: SQL query to get class, description, attribute & tagged values
« Reply #13 on: December 05, 2017, 08:38:20 pm »
Look into %appdata%/..sparx.../dberror.txt. That might have more info.

q.