How to produce a product breakdown structure in a table

[Modesto Vega]

Hi,
Sorry to break up the party, but SQL Server does not support CTE ( Common Table Expressions).
When I first saw this solution, my first thought was "that ain't gonna work", until I read on that this is for Oracle or some other DB.
I used to use CTE very often (in DB2). It makes the queries so much easier to understand, test and debug. It is a pity that Microsoft chooses not to support it.
Shimon

SQL Server supports CTEs (Common Table Expressions) and has done so since version 2005 - i.e., 20 years.

The issue with Roche's query is that Sparx EA does not support any SQL Statement that does not start with a SELECT statement - i.e., it does not support a SQL starting with "WITH". In my opinion, it is already time Sparx Systems addresses this issue and does not wait until Microsoft introduces support for the syntax used by Roche.

I have taken Roche's query and refactor it into a SQL Server view which I currently cannot post here and can be used in a ModelView.

Happy to share it after Sparx Systems fixes the security issue with this forum preventing creating posts with code samples including SQL Statements.

[Eve]

Happy to share it after Sparx Systems fixes the security issue with this forum preventing creating posts with code samples including SQL Statements.

I have nothing to do with the administration for our website, but from what I'm aware what you're describing as a security issue is that the Cloudflare SQL injection protection is stronger than you would like in this particular instance.

Yes, it's technically an issue that you are experiencing with the security for the forum. But what you seem to be asking for is removing/weakening that security.

[Modesto Vega]

Yes, it's technically an issue that you are experiencing with the security for the forum. But what you seem to be asking for is removing/weakening that security.

Just to be clear, I am not asking and will never ask for weaking security. However, Reddit and Stack Overflow support including SQL statements and do not class it as a SQL Injection, and, from a user functionality point of view, I do not see why this forum should no longer support the inclusion of SQL Statements in posts.

Furthermore, the implication is that the software running the forum, which was not written by Sparx Systems, is unsafe, subject to SQL injections.

[shimon]

Thanks Modesto,
You are indeed right. It is Access that does not support CTE, and I thought that it was MS-SQL.

[Eve]

Just to be clear, I am not asking and will never ask for weaking security. However, Reddit and Stack Overflow support including SQL statements and do not class it as a SQL Injection, and, from a user functionality point of view, I do not see why this forum should no longer support the inclusion of SQL Statements in posts.

You are explicitly advocating to remove a security function. It may be excessive in its implementation but that doesn't change that it is still a security function.

Furthermore, the implication is that the software running the forum, which was not written by Sparx Systems, is unsafe, subject to SQL injections.

No, I'm stating that the cautious approach is to assume that unknown vulnerabilities may exist in any software.

[Modesto Vega]

Thanks Modesto,
You are indeed right. It is Access that does not support CTE, and I thought that it was MS-SQL.

Thank you for confirming, Shimon.

[Modesto Vega]

Just to be clear, I am not asking and will never ask for weaking security. However, Reddit and Stack Overflow support including SQL statements and do not class it as a SQL Injection, and, from a user functionality point of view, I do not see why this forum should no longer support the inclusion of SQL Statements in posts.

You are explicitly advocating to remove a security function. It may be excessive in its implementation but that doesn't change that it is still a security function.

Furthermore, the implication is that the software running the forum, which was not written by Sparx Systems, is unsafe, subject to SQL injections.

No, I'm stating that the cautious approach is to assume that unknown vulnerabilities may exist in any software.

11 June 2025 there were at least 2 posts with SQL Statements: https://sparxsystems.com/forums/smf/index.php/topic,49043.msg283626.html#msg283626 and https://sparxsystems.com/forums/smf/index.php/topic,49042.msg283625.html#msg283625, with the 2nd one having a post  with a SQL Statement dated 13 June 2025. Since then, the security was tightened, intentionally or accidentally. If intentionally the result is that some forum functionality was removed.

I am arguing for having functionality used by many of us for years restored.