Author Topic: Creating recursive SQL Queries with CTE  (Read 156 times)

max

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Creating recursive SQL Queries with CTE
« on: April 08, 2021, 12:46:49 am »
I would like to get all the subelements of an selected element (i.e. all elements which are connected by the traceability status "needed-by"). In order to achieve this, I planned to use a vb script and the "Repository.SQLQuery()" function. As input for this function I wanted to use a recursive SQL query with CTE (see the snippet below) to get all of the subelements/children and their subelements in every "layer". The problem is, that EA does not support recursive queries with CTE (every query in EA must start with SELECT).

I found a post (https://www.sparxsystems.com/forums/smf/index.php?topic=38324.0) saying that a recursive SQL query is possible in EA by creating a database view. In this view one can use the WITH term and then call it from EA with an SELECT term. I could not reproduce this example, how would that work?

Is there another way to achieve a recursive SQL query in EA?


The recursive SQL query I planned on using:
Code: [Select]
WITH ChildrenAndTheirParents (child_ID, parent_ID, depth) AS
(SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, 0
FROM t_connector INNER JOIN t_Object
ON t_connector.Start_Object_ID = t_Object.Object_ID
AND t_Object.Object_ID = xxx
AND con.Connector_Type = 'Dependency'
UNION ALL
SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, ChildrenAndTheirParents.depth+1
FROM ChildrenAndTheirParents INNER JOIN t_connector
ON ChildrenAndTheirParents.child_ID = t_connector.End_Object_ID
INNER JOIN t_Object
ON ChildrenAndTheirParents.parent_ID = t_Object.Object_ID)
SELECT * FROM ChildrenAndTheirParents
ORDER BY depth

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 10899
  • Karma: +387/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Creating recursive SQL Queries with CTE
« Reply #1 on: April 08, 2021, 04:05:12 am »
I don't know a solution, but from experience, there usually is a workaround for it.
The most common one is to find how deep your hierarchy goes, and then add a few to that. then do as many regular joins as that.

I've done that quite a few times. It's not pretty, but it works.

Geert


qwerty

  • EA Guru
  • *****
  • Posts: 11738
  • Karma: +322/-274
  • I'm no guru at all
    • View Profile
Re: Creating recursive SQL Queries with CTE
« Reply #2 on: April 08, 2021, 04:12:22 am »
Instead of banging my head to get such a beast working I just fall back to scripting with some appropriate simple SQL. For me that always work in top performance.

q.

Sunshine

  • EA Practitioner
  • ***
  • Posts: 1038
  • Karma: +97/-8
  • Its the results that count
    • View Profile
Re: Creating recursive SQL Queries with CTE
« Reply #3 on: April 08, 2021, 08:47:49 am »
You should just try that SQL and see if it works - The proof in the pudding is in the tasting after all.

The exact SQL syntax is highly dependent on the repository DB you use. You'll find that SQL syntax for MS Access (native eapx file), SQL Server, MySQL and Oracle DB can all vary. For example I've found stuff that works in MS Access needs tweaking or re-writing for SQL server or Oracle. BTW you haven't stated what DB engine you are using.

Similar to Qwerty I either use scripts such as JavaScript or JScript only and on some occasions use embed simple SQL to improve performance. Sparx provide examples of scripts for recursively navigating so that's the easier path I find although performance might be a little slower than pure SQL at least it works.

There is a saying that goes something along the lines of "Its easier to optimise a working system than to get an optimised system to work". I think by going down the SQL with CTE path you are attempting the latter so be prepared to put in a lot of effort.

When you get into the space of more advanced SQL your best bet it to go to the SQL forum's for the DB you are using.
You'll have to do your own trial and error with advanced SQL in Sparx EA I'm afraid. It can be quite time consuming, so good luck with your advanced SQL and do share any successes with the forum.



Happy to help
:)

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 10899
  • Karma: +387/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Creating recursive SQL Queries with CTE
« Reply #4 on: April 08, 2021, 02:12:56 pm »
You should just try that SQL and see if it works - The proof in the pudding is in the tasting after all.
It really doesn't work, I've tried it in the past as well. :-\

Geert

OpenIT Solutions

  • EA User
  • **
  • Posts: 532
  • Karma: +5/-1
    • View Profile
Re: Creating recursive SQL Queries with CTE
« Reply #5 on: April 08, 2021, 10:32:33 pm »
Hi,

In a tool like SQL Server management Studio, open a connection to your Sparx DB with an account that has permission to create views. Then wrap your CTE in a view, ie:

CREATE VIEW ChildrenAndTheirParents AS
WITH ChildrenAndTheirParents (child_ID, parent_ID, depth) AS
(SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, 0
FROM t_connector INNER JOIN t_Object
ON t_connector.Start_Object_ID = t_Object.Object_ID
AND t_Object.Object_ID = xxx
AND con.Connector_Type = 'Dependency'
UNION ALL
SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, ChildrenAndTheirParents.depth+1
FROM ChildrenAndTheirParents INNER JOIN t_connector
ON ChildrenAndTheirParents.child_ID = t_connector.End_Object_ID
INNER JOIN t_Object
ON ChildrenAndTheirParents.parent_ID = t_Object.Object_ID)
SELECT * FROM ChildrenAndTheirParents

Then in Sparx just SELECT * FROM ChildrenAndTheirParents

Regards,

Jon.

Sunshine

  • EA Practitioner
  • ***
  • Posts: 1038
  • Karma: +97/-8
  • Its the results that count
    • View Profile
Re: Creating recursive SQL Queries with CTE
« Reply #6 on: April 09, 2021, 06:59:00 am »
Hi,

In a tool like SQL Server management Studio, open a connection to your Sparx DB with an account that has permission to create views. Then wrap your CTE in a view, ie:

CREATE VIEW ChildrenAndTheirParents AS
WITH ChildrenAndTheirParents (child_ID, parent_ID, depth) AS
(SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, 0
FROM t_connector INNER JOIN t_Object
ON t_connector.Start_Object_ID = t_Object.Object_ID
AND t_Object.Object_ID = xxx
AND con.Connector_Type = 'Dependency'
UNION ALL
SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, ChildrenAndTheirParents.depth+1
FROM ChildrenAndTheirParents INNER JOIN t_connector
ON ChildrenAndTheirParents.child_ID = t_connector.End_Object_ID
INNER JOIN t_Object
ON ChildrenAndTheirParents.parent_ID = t_Object.Object_ID)
SELECT * FROM ChildrenAndTheirParents

Then in Sparx just SELECT * FROM ChildrenAndTheirParents

Regards,

Jon.
Good idea sounds like that might work.
Happy to help
:)

max

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: Creating recursive SQL Queries with CTE
« Reply #7 on: April 09, 2021, 08:32:44 pm »
I don't know a solution, but from experience, there usually is a workaround for it.
The most common one is to find how deep your hierarchy goes, and then add a few to that. then do as many regular joins as that.

I've done that quite a few times. It's not pretty, but it works.

Geert

Yes, I already wrote a script which uses a similar method. Basically it's just a loop, which concatenates sql query phrases with a lot of joins as a string.  It's working, but I have to agree with you, it's really not a neat solution.

max

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: Creating recursive SQL Queries with CTE
« Reply #8 on: April 09, 2021, 08:34:04 pm »
Hi,

In a tool like SQL Server management Studio, open a connection to your Sparx DB with an account that has permission to create views. Then wrap your CTE in a view, ie:

CREATE VIEW ChildrenAndTheirParents AS
WITH ChildrenAndTheirParents (child_ID, parent_ID, depth) AS
(SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, 0
FROM t_connector INNER JOIN t_Object
ON t_connector.Start_Object_ID = t_Object.Object_ID
AND t_Object.Object_ID = xxx
AND con.Connector_Type = 'Dependency'
UNION ALL
SELECT t_connector.Start_Object_ID, t_connector.End_Object_ID, ChildrenAndTheirParents.depth+1
FROM ChildrenAndTheirParents INNER JOIN t_connector
ON ChildrenAndTheirParents.child_ID = t_connector.End_Object_ID
INNER JOIN t_Object
ON ChildrenAndTheirParents.parent_ID = t_Object.Object_ID)
SELECT * FROM ChildrenAndTheirParents

Then in Sparx just SELECT * FROM ChildrenAndTheirParents

Regards,

Jon.

Hi Jon,

will look into that, thanks!