Get all elements of hierarchy

[Geert Bellekens]

The old version used .eap, which is a ms access database and thus requires MS Access SQL Syntax (which is horrible and requires a lot of parentheses)

Recently the default file format is .qea which is sqlite. This database uses a lot more standard SQL compared to Access.

Geert

[qwerty]

Where, honestly, SQL has not really achieved a standard but different flavors in each machine. It's just that some have more in common than others. M$Access is clearly an outsider.

q.

[Elpis]

You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

But, (surprisingly?) you can use recursive queries in Repository.GetElementSet (checked in EA 16.1). However, there are limitations of such a queries, too (seems to be imposed by EA pre-processing of the SQL). E.g. you can't use square brackets in sql syntax (as, for example, in ARRAY expression in PostgreSQL).

[Elpis]

You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

You can cheat EA with internal query syntax, like below:

Code: [Select]

SELECT q.* FROM (
  <any_query_not_starting_with_select_keyword>
) q;

It works on PostgreSQL repository, and I'm pretty sure, this syntax (or similar) is possible in many other SQL engines.

[Geert Bellekens]

You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

You can cheat EA with internal query syntax, like below:

Code: [Select]

SELECT q.* FROM (
  <any_query_not_starting_with_select_keyword>
) q;

It works on PostgreSQL repository, and I'm pretty sure, this syntax (or similar) is possible in many other SQL engines.

I know I tried this a few years ago and couldn't get it to work for searches in SQL Server. I never really bothered anymore once the #Branch# macro became available.

Geert

[Richard Freggi]

You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

You can cheat EA with internal query syntax, like below:

Code: [Select]

SELECT q.* FROM (
  <any_query_not_starting_with_select_keyword>
) q;

It works on PostgreSQL repository, and I'm pretty sure, this syntax (or similar) is possible in many other SQL engines.

I know I tried this a few years ago and couldn't get it to work for searches in SQL Server. I never really bothered anymore once the #Branch# macro became available.

Geert

Tried it in EA 16.1 on local SQLite project: EA crashed... too bad...

[Modesto Vega]

You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

Geert

I have not tried this yet, but couldn't a view be used with a "proper" database - e.g., SQL Server or Oracle - to wrap the any queries not starting with a select?

[Geert Bellekens]

You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

Geert

I have not tried this yet, but couldn't a view be used with a "proper" database - e.g., SQL Server or Oracle - to wrap the any queries not starting with a select?

The problem with a view, is that you are starting to change the "standard" structure of the database as provided by Sparx. So whenever you project transfer, you have to remember to drag your database customizations with you.

I generally try to avoid this whenever possible, and in this case there are other alternatives.

Geert