Enterprise Architect Database SQL Issue

[AxelBM]

Hi,

we plan to switch from SVN Version control to a Enterprise Architect Database.

Unfraternally most of our Dashboard SQL-Statements are not functional any more since we switch to a Microsoft SQL Database.

One example is this SQL Statement:

select
   sum((select count(*) from t_operation a where a.object_id = o.object_id)) as Operations_count,
   sum((select count(*) from t_object a where a.object_type = 'Activity' and a.ParentId = o.object_id)) as ActicityDiagram_count
from t_object o
where o.Object_Type = 'Class' and o.Name Not Like '%_t%' and ( o.Stereotype <> 'Specification' or o.Stereotype is null)


We got the Error:
SQL API Open FAILED: Cannot perform an aggregate function on a expression containing an aggregate or a subquery.

Is there a workaround available?
Are there options available to enable aggregate functions?
Why is this statement functional on File-Based EA?

EA Version: 16.1.1622
Microsoft SQL Database

Regards Axel

[Geert Bellekens]

File based (.eap file) is MS Access.
There are quite some differences in SQL syntax and functionality between MS Access and SQL Server.

You'll definitely be able to do anything you can do with MS Access with SQL Server, but you'll need to adjust the syntax and/or construction of your queries.
I don't know immediately the solution for this one, but I'm convinced there is one.

Geert

[Richard Freggi]

ChatGPT to convert DDL code from one DBMS to another?  Never tried it but may be worth a shot...

[Elpis]

we plan to switch from SVN Version control to a Enterprise Architect Database.

I'm not sure what do you mean by that 'switch', as "SVN" and "EA Database" are beings from different categories. They may be combined into a one solution, but they are not substitutable each by other.

I guess, you want to switch from one EA repository _RDBMS_ into another. From what to what? Is "Microsoft SQL Database" an "MS Jet" aka Access database (*.eap file)? Is it the source 'format' you want to switch from or is it a target (to-be)?