Author Topic: How use tagged-value in SQL-script for Chart diagram  (Read 1029 times)

steen.jensen@sll.se

  • EA User
  • **
  • Posts: 52
  • Karma: +1/-1
    • View Profile
How use tagged-value in SQL-script for Chart diagram
« on: May 10, 2018, 08:49:42 am »
I going crazy, Im trying to create some simple charts from a package of requirements and a specific tagged-value: ie. 'TAG-Area'
The example from Sparx is not helping.... As this does not show how to use tagged-values in SQL-script??

Select t_object.Status AS Series, t_object.Author AS GroupName from t_object

Nizam

  • Prolab Moderator
  • EA User
  • *
  • Posts: 301
  • Karma: +9/-2
  • Model Sharing - Simplified
    • View Profile
    • Professional Model Collaboration
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #1 on: May 10, 2018, 08:51:43 am »
Try this
Code: [Select]
SELECT tv.Value AS Series, tv.Property as GroupName
FROM t_objectproperties tv
INNER JOIN t_object o ON o.Object_ID = tv.Object_ID
WHERE tv.Property in ('Critical')

steen.jensen@sll.se

  • EA User
  • **
  • Posts: 52
  • Karma: +1/-1
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #2 on: November 17, 2018, 09:19:05 am »
Thanks Nizam, works perfectly.
I still not understand the secrets with SQL script and Inner joins, but I studie  ;D

steen.jensen@sll.se

  • EA User
  • **
  • Posts: 52
  • Karma: +1/-1
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #3 on: November 17, 2018, 09:23:08 am »
Whats the meaning with the "o" on both sides of "ON"
 Still confusing  :o

qwerty

  • EA Guru
  • *****
  • Posts: 9877
  • Karma: +191/-156
  • I'm no guru at all
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #4 on: November 17, 2018, 10:37:03 am »
It's a synonym. A shortcut for "AS o".  "t_object ON t_object.Object_ID"  would be the same. But sometimes you need a table more than once. In that case you'd use e.g. "t_object AS o1" in one place and "t_object AS o2" in the other.

q.

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 6518
  • Karma: +57/-6
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #5 on: November 19, 2018, 10:14:15 am »
It's a synonym. A shortcut for "AS o".  "t_object ON t_object.Object_ID"  would be the same. But sometimes you need a table more than once. In that case you'd use e.g. "t_object AS o1" in one place and "t_object AS o2" in the other.

q.
The interesting thing is, at least one of the databases EA connects to will give an error if you use "as" with a table alias. If you're writing cross platform sql, it's better not to include the as here. In contrast aliases on column names are the reverse. You need to include the "as" or at least one database type doesn't handle it.
Eve

support@sparxsystems.com

qwerty

  • EA Guru
  • *****
  • Posts: 9877
  • Karma: +191/-156
  • I'm no guru at all
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #6 on: November 19, 2018, 10:24:40 am »
And on top of it there is that magical upper case AS which is required by EA (don't remember the exact circumstances) xD

q.

KP

  • EA Administrator
  • EA Expert
  • *****
  • Posts: 2553
  • Karma: +34/-2
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #7 on: November 19, 2018, 10:43:22 am »
And on top of it there is that magical upper case AS which is required by EA (don't remember the exact circumstances) xD

q.

You're thinking of the SELECT ea_guid AS CLASSGUID, ... trick to make query results double-clickable.
The Sparx Team
support@sparxsystems.com

steen.jensen@sll.se

  • EA User
  • **
  • Posts: 52
  • Karma: +1/-1
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #8 on: January 12, 2019, 09:34:22 am »
I got this working SQL snippet that did my needed magic for status dashboards ;D :
Quote
Code: [Select]
SELECT tv.Value AS Series, tv.Property as GroupName
FROM t_objectproperties tv
INNER JOIN t_object o ON o.Object_ID = tv.Object_ID
WHERE tv.Property in ('Critical')
Last Edit: May 10, 2018, 09:09:43 am by Nizam

Next question is, if anyone knows how to make this SQL-script start at specific package and just travers down to last child package ??
My colleges that know ordinary SQL-script does not understand the Sparx EA object model and I dont know scripting  :P

qwerty

  • EA Guru
  • *****
  • Posts: 9877
  • Karma: +191/-156
  • I'm no guru at all
    • View Profile
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #9 on: January 12, 2019, 10:47:14 am »
Well, that's sort of possible, but a PITA. You can "recurse" to a certain level by looking at the t_package.packageId which points up to the parent package. This way you can write a "fixed recursion" to a certain fixed depth. But besides from that scripting is the way to go. I remember that Geert posted something along these lines. He'll likely drop in this morning.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8677
  • Karma: +211/-26
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How use tagged-value in SQL-script for Chart diagram
« Reply #10 on: January 12, 2019, 05:20:08 pm »
Well, that's sort of possible, but a PITA. You can "recurse" to a certain level by looking at the t_package.packageId which points up to the parent package. This way you can write a "fixed recursion" to a certain fixed depth. But besides from that scripting is the way to go. I remember that Geert posted something along these lines. He'll likely drop in this morning.

q.
In some circumstances you can use the macro #Branch# which expands to a comma separated list of package ID's of the current package and all packages underneath.
Not sure if it applies here.

Otherwise it's as qwerty suggested. Join packages to a deep enough level, or use scripting (if that is possible in this case, I'm not sure)

Geert