Sparx Systems Forum

Enterprise Architect => Bugs and Issues => Topic started by: kotrick on January 10, 2014, 05:43:50 pm

Title: DAO.QueryDef[3131] While executing query in .eap
Post by: kotrick on January 10, 2014, 05:43:50 pm

Hello,
I have a very complex query with subqueries,JOINS used in that which executes succesfully when EA is in oracle.

But if i execute same query in .eap file it throws below error
DAO.QueryDef[3131] Syntax error in FROM clause

Can anyone please help or provide suggestion to solve this issue

Title: Re: DAO.QueryDef[3131] While executing query in .e
Post by: Helmut Ortmann on January 10, 2014, 05:58:13 pm

Hi,

in %appdata%Sparx System\EA\dberr.txt
you see the SQL string and the original error message.

In complex queries you usually have to adopt the query to your DB (ORACLE). You can do this by a macro like #DB=SQLSVR# (only if you use different DBs).

Another possibility is to install the Oracle developer to test your query with a native tool. For complex queries this is my preferred way.

Helmut

Title: Re: DAO.QueryDef[3131] While executing query in .e
Post by: kotrick on January 10, 2014, 07:10:02 pm

Hello,
I am able to execute query without any errors in oracle and also if Enterprise Architect Project is in oracle

But if i run the same query in .eap file EA throws the error which i have specified in subject line.
I did not understand your reply can you please suggest clearly how i can handle the issue

Title: Re: DAO.QueryDef[3131] While executing query in .e
Post by: Helmut Ortmann on January 10, 2014, 08:56:29 pm

Hello,

use the macros:
#DB=ORACLE# for Oracle specific things
#DB=JET# for *.eap specific things

As I remeber right *.eap (JET/ACCESS) has problems with INNER JOINS. You may replace them by a where clause. There are some other differences. Look at the error text in dberr.txt.

Helmut

Title: Re: DAO.QueryDef[3131] While executing query in .e
Post by: Geert Bellekens on January 10, 2014, 11:09:11 pm

Access supports inner joins perfectly, as long as you remember to put the "inner", and add enough parentheses.

Something like

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.name as Name
,package.name as 'Package Name' ,package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from (((((t_object o
left join t_xref stereo on stereo.Client = o.ea_guid)
inner join t_package package on o.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where
o.Stereotype like '#WC#<Search Term>#WC#'
or stereo.description like '@STEREO;Name=#WC#<Search Term>#WC#;#WC#'Should work in Oracle, SQL Server and .eap

Geert