Author Topic: Using personal, fixed #Branch# Keyword for SQL Queries  (Read 6489 times)

wzr

  • EA Novice
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Using personal, fixed #Branch# Keyword for SQL Queries
« on: June 24, 2021, 09:25:57 pm »
Hello,

we would like to limit SQL Queries in Charts and ModelViews to specific packages (including sub-packages) in our model.
In fact we want to do the same like #Branch# is doing for the current selection, i.e.:
select * from t_object where t_object.Package_ID in (#Branch#)

But we want to be independent from the current selection, so we used to generate the required Package-ID list by script and use this fixed string in our queries, i.e.
select * from t_object where t_object.Package_ID in (6220, 6215)

Obviously this is a temporary solution, because we need to adapt our queries evey time we add or remove packages.

So the idea is to generate and store the Package-ID list in the Notes of Elements and use this in our queries:
select * from t_object where t_object.Package_ID in (select t_object.Note from t_object where t_object.Name = 'MyBranchElement')

With this we can use our Branch-Elements in several queries. After changing the package hierachy we only need to regenerate the ID-List in our Branch-Elements and all queries are up to date.
Unfortunately this only works for a single ID, because if the Notes contains a comma seperated list of IDs the result of the inner select statement is a string which can't be interpreted as a list of integers and results in an error message.
In fact it results in a query like this which doesn't work:
select * from t_object where t_object.Package_ID in ('6220, 6215')

Is there any possibility to interprete the Notes-String as a list of integers instead of a string? It should be handled like replacing the inner select statement with its result!?

Thank you!

Best Regards

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11315
  • Karma: +422/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #1 on: June 24, 2021, 09:57:34 pm »
I don't think your approach is very sustainable. What if you move a package from under the branch? What if the packageID's are being recalculated (yes that happens, the package ID's are not stable)

A better approach would be to join a couple of levels of packages, starting from the selected element
The only downside of this approach is that you have to work with a fixed maximum set of package levels.
But if you currently need maximum 5 level, you could write the query for 7 levels to be safe.

Code: [Select]
SELECT o2.name, o2.ea_guid
FROM t_object o
INNER JOIN t_package p ON p.Package_ID = o.Package_ID
LEFT JOIN t_package p1 ON p1.Parent_ID = o.Package_ID
LEFT JOIN t_package p2 ON p2.Parent_ID = p1.Package_ID
LEFT JOIN t_package p3 ON p3.Parent_ID = p2.Package_ID
LEFT JOIN t_package p4 ON p4.Parent_ID = p3.Package_ID
LEFT JOIN t_package p5 ON p5.Parent_ID = p4.Package_ID
inner join t_object o2 on o2.Package_ID in (p1.Package_ID, p2.Package_ID, p3.Package_ID, p4.Package_ID, p5.Package_ID)
where o.ea_guid = '{5DB9167B-CE46-4a15-B691-C3477430ACBE}'

In this case "o" would be the selected element

Geert

wzr

  • EA Novice
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #2 on: June 25, 2021, 02:33:57 am »
Hi Geert,

thanks for your reply.

Actually i thought this is the most sustainable approach compared to our other approaches :)
The idea is to have a script that updates the Notes (Package-ID-List) of the "Branch-Elements" automatically (i.e. on startup via model add in, or manually executed). So if something changes with the IDs it will be covered by the update.

I think my SQL mindset is not ready yet for a complete understanding of your JOIN solution  :-\
When I try to use this one for just giving me all names and guids of elements in a specific package x (and underlaying sub-packages), I replace the guid in your example with the guid of the "package x"-object. As a result I get all elements in the parent package of x and its sub-packages (including package x) which is one level to high. When I try the guid of an element in package x instead, there is no result at all.
Probably I need to become more firm with these JOINS.

The additional complexity in a query to limit the search results to a specific part of the model seems to be quite high.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11315
  • Karma: +422/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #3 on: June 25, 2021, 02:44:07 am »
The script-updates the notes approach seems to have a lot of moving parts.

If I were you I would invest in your SQL skills. I'm sure it will pay you back in no time.
Good SQL skills are crucial for any advanced EA usage.

Geert

qwerty

  • EA Guru
  • *****
  • Posts: 12446
  • Karma: +348/-291
  • I'm no guru at all
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #4 on: June 25, 2021, 05:21:38 am »
Even with limited knowledge you gain a lot. I can tell you :-)

q.

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7615
  • Karma: +96/-18
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #5 on: June 25, 2021, 08:28:03 am »
15.2 added the capability to do that.

https://sparxsystems.com/enterprise_architect_user_guide/15.2/navigation/creating_filters.html
Quote
Gets the ID of each child Package under one or more parent Packages, working recursively down to the lowest level of sub-Package. For example:

t_object.Package_ID in (#Branch#)

There are three permutations of this macro:
  • in #Branch# - gets the ID of each child Package of the parent Package selected by the user
  • in #Branch=<GUID># or #Branch=<ID># - gets the ID of each child Package of the parent Package specified by the GUID or ID
  • in #Branch=<ID>,<ID>,<ID># - gets the ID of each child Package under each parent Package specified by its ID
« Last Edit: June 25, 2021, 09:34:13 am by Eve »
Eve

support@sparxsystems.com

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11315
  • Karma: +422/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #6 on: June 25, 2021, 02:10:50 pm »
15.2 added the capability to do that.

https://sparxsystems.com/enterprise_architect_user_guide/15.2/navigation/creating_filters.html
Quote
Gets the ID of each child Package under one or more parent Packages, working recursively down to the lowest level of sub-Package. For example:

t_object.Package_ID in (#Branch#)

There are three permutations of this macro:
  • in #Branch# - gets the ID of each child Package of the parent Package selected by the user
  • in #Branch=<GUID># or #Branch=<ID># - gets the ID of each child Package of the parent Package specified by the GUID or ID
  • in #Branch=<ID>,<ID>,<ID># - gets the ID of each child Package under each parent Package specified by its ID
Nice, that a really useful feature.

Geert

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7615
  • Karma: +96/-18
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #7 on: June 25, 2021, 04:32:40 pm »
We do occasionally make useful changes.  ;)
Eve

support@sparxsystems.com

qwerty

  • EA Guru
  • *****
  • Posts: 12446
  • Karma: +348/-291
  • I'm no guru at all
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #8 on: June 25, 2021, 06:24:53 pm »
I was just flying over that page, but basically it looks like it describes the #branch# for SQL queries as it has been there ever since. So only in the self-defined SQL searches. But nowhere else. Am I wrong?

q.

P.S. Looks more or less the same as https://sparxsystems.com/enterprise_architect_user_guide/13.5/model_navigation/creating_filters.html
« Last Edit: June 25, 2021, 06:26:59 pm by qwerty »

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11315
  • Karma: +422/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #9 on: June 25, 2021, 08:07:13 pm »
I think these two variants are new

Quote
in #Branch=<GUID># or #Branch=<ID># - gets the ID of each child Package of the parent Package specified by the GUID or ID
in #Branch=<ID>,<ID>,<ID># - gets the ID of each child Package under each parent Package specified by its ID

Which solves wzr's problem.

Geert

qwerty

  • EA Guru
  • *****
  • Posts: 12446
  • Karma: +348/-291
  • I'm no guru at all
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #10 on: June 25, 2021, 09:28:17 pm »
Ah, thanks Geert! Those are new indeed. Maybe Sparx could make some bling-bling so even my aged eyes recognize it as a new feature ;-) (No, please don't!)

q.

qwerty

  • EA Guru
  • *****
  • Posts: 12446
  • Karma: +348/-291
  • I'm no guru at all
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #11 on: June 25, 2021, 09:32:42 pm »
Uh, oh. Partially it was me doing something wrong when trying the SQL. But then: CASE matters!

SELECT * from t_object where t_object.Package_ID in (#Branch#) works. But
SELECT * from t_object where t_object.Package_ID in (#branch#) does not.

Well, somehow it's still EA xD

q.

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7615
  • Karma: +96/-18
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #12 on: June 29, 2021, 09:19:20 am »
Maybe Sparx could make some bling-bling so even my aged eyes recognize it as a new feature ;-)
But then: CASE matters!
Do we also need to add some bling-bling so that you can see the bit of the help that tells you that?  ;)
Quote
These macros are all case-sensitive.
Eve

support@sparxsystems.com

qwerty

  • EA Guru
  • *****
  • Posts: 12446
  • Karma: +348/-291
  • I'm no guru at all
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #13 on: June 29, 2021, 05:54:14 pm »
Seems like so. My remaining life span might be short but my patience in reading is even shorter. The reason I was pointing that out (despite being documented in the help) is the fact that EA behaves consequently inconsequent also when it comes to casing. Be it this place (strictly sensitve), stereotypes (sensitive depending on the weather) or shape scripts (completely insensitive). Makes it as hard to remember as the use of underlines and plural in the database. Having style guides is not a thing of Sparx, is it?

q.
« Last Edit: June 29, 2021, 08:17:30 pm by qwerty »

wzr

  • EA Novice
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Using personal, fixed #Branch# Keyword for SQL Queries
« Reply #14 on: July 27, 2021, 08:02:34 pm »
Sorry I'm late  :-[
Anyway, thanks to everyone.

The new possibilities of 15.2 in this point are very usefull for us.
Maybe we can change to that release. Nevertheless I had to accept that I need to take a deeper step into SQL.