SQL Query question

[kewldee]

Hi, I recently started using SQL queries for obtaining custom extracts. Since I am querying more and more everyday I decided to move to a local project and design the query there before running them against the prod DB (Shared Space / Cloud). I used the XML import / export feature to create a local copy of all our objects and diagrams. I am running into an issue where a query runs in the shared space but the exact same query errors out in my local folder. More specifically, the second Inner Join of the two joins I have is causing issues. If I take that off the query runs just fine.

 1. Any thoughts on what's causing an error in local project but not on Shared space (for the same exact query)? Any config that needs to be done differently so the queries run in both environments.


Thanks
Kewl

[KP]

When you say you created a local copy, do you mean an .EAP file? That is an MS Access database and will have a different SQL dialect to your cloud database.

[kewldee]

When you say you created a local copy, do you mean an .EAP file? That is an MS Access database and will have a different SQL dialect to your cloud database.

Yeah an .EAP file. And I see your point abt a different SQL dialect. No wonder it was causing errors. 

So what are my options if I want to work on a local project first? Will installing some DBMS help at all ?

[qwerty]

You can put #<db-type># inside like #ORACLE# which are digested by EA and sent to the dedicated target only. See the manual for the options.

q.

[Geert Bellekens]

I have a local SQL Server Express running to develop queries.

There is a way to make most SQL queries be accepted by most databases though.
The trick is to use plenty of parentheses

Code: [Select]

select * from ((t_object o
inner join t_object o2 on o2.parentID = o.Object_ID)
inner join t_object o3 on (o3.parentID = o2.Object_ID
                                    and o3.Stereotype = 'myStereot'))One '(' for each table you join, and another for multiple join conditions.

Also use #WC# for wildcard (will be replaces by * or % depending on the database you run it on)

Geert

[matthew.james]

Also use #WC# for wildcard (will be replaces by * or % depending on the database you run it on)

Out of curiosity (cos we're about to try moving from .eap to SQL Server)
- Is this a Sparx feature (substituting the #WC# for the right wildcard character) or is it done by the database driver?
- Does anyone know if Sparx .eap files can be configured to use ANSI-92 syntax (matches SQL Server, incl. the right wildcards) rather than ANSI-89 syntax (the non-standard Access syntax)

[Paolo F Cantoni]

Also use #WC# for wildcard (will be replaces by * or % depending on the database you run it on)

Out of curiosity (cos we're about to try moving from .eap to SQL Server)
- Is this a Sparx feature (substituting the #WC# for the right wildcard character) or is it done by the database driver?
- Does anyone know if Sparx .eap files can be configured to use the ANSI-92 syntax (matches SQL Server, incl. the right wildcards) rather than ANSI-89 syntax (the non-standard Access syntax)

(my emphasis)
We have our .eap files set for ANSI-92 syntax and it is extremely rare that our queries don't work in both (Access and MS SQL Server).  In over a decade, and developing literally thousands of queries, I could count on one hand those that didn't work in both.
Where there IS a discrepancy, the query can usually be recast to work in both.
We use an MS Access DB with linked tables to our repositories.

HTH,
Paolo

[qwerty]

The #-thingy is Sparxian. They parse the SQL before sending it to the connected database. Since they know which it is (you configured it before in EA) the know what the wildcard is (#WC#) and which specific part (#ORACLE# etc.) to send.

q.

[kewldee]

I have a local SQL Server Express running to develop queries.

There is a way to make most SQL queries be accepted by most databases though.
The trick is to use plenty of parentheses

Code: [Select]

select * from ((t_object o
inner join t_object o2 on o2.parentID = o.Object_ID)
inner join t_object o3 on (o3.parentID = o2.Object_ID
                                    and o3.Stereotype = 'myStereot'))One '(' for each table you join, and another for multiple join conditions.

Also use #WC# for wildcard (will be replaces by * or % depending on the database you run it on)

Geert

Sweet. Works like a charm with the extra parenthesis. Thanks.

[kewldee]

You can put #<db-type># inside like #ORACLE# which are digested by EA and sent to the dedicated target only. See the manual for the options.

q.

Ok. Thanks. I will refer to the manual and see how this works.