SqlQuery works from UI but not using API

[Rudi Larno - Euricom]

I have a Search defined using the following SQLQuery:

Code: [Select]

select o.ea_guid as CLASSGUID, o.name as Name
, p.name as 'Package Name', p.Package_ID
, p1.name as 'Package level -1', p1.Package_ID
, p2.name as 'Package level -2', p2.Package_ID
, p3.name as 'Package level -3', p3.Package_ID
from (((( t_object  o
inner join t_package  p on o.package_id = p.package_id)
left join t_package  p1 on p1.package_id = p.parent_id)
left join t_package  p2 on p2.package_id = p1.parent_id)
left join t_package  p3 on p3.package_id = p2.parent_id)
where o.Object_Type = 'Requirement'
order by p3.package_id, p2.package_id, p1.package_id, p.package_id
When running this query in the UI (Model Search) I get the expected results. If I put the query in my code (C#) and use the Repository.SQLQuery method, I get many error dialog boxes with a bit of useless info:

---------------------------
EA
---------------------------
Error:
Code = 0x0
Source = Line : 0; Char : 0
Error Description = (null)

---------------------------
OK  
---------------------------

==> Help! How can I get this query to work using

EA version 8.0.864

Note: I'd like to use this query to quickly build up a treeview with checkboxes of packages with requirements in them as enumerating the EA model is really slow.

ps: Thanks to Geert for getting the Left Join syntax in there
http://community.sparxsystems.com/resources/model-search/search-operations-parameter-type

[Rudi Larno - Euricom]

Ok, been looking why it failed for half an hour, decided to post to this forum, and THEN I find the answer myself... duh!  

So it turned out while I did get all these popup's, it tended to be 3 times per item I expected and I just noticed that I did get an XML result back.

So the aliases for the package.name are the evil doers. This qurey works as expected:

Code: [Select]

select o.ea_guid as CLASSGUID, o.name as Name
, p.name , p.Package_ID
, p1.name , p1.Package_ID
, p2.name , p2.Package_ID
, p3.name , p3.Package_ID
from (((( t_object  o
inner join t_package  p on o.package_id = p.package_id)
left join t_package  p1 on p1.package_id = p.parent_id)
left join t_package  p2 on p2.package_id = p1.parent_id)
left join t_package  p3 on p3.package_id = p2.parent_id)
where o.Object_Type = 'Requirement'
order by p3.package_id, p2.package_id, p1.package_id, p.package_id
Yippie!! Now I can get back to create business value.

[Geert Bellekens]

Rudi,

I guess it depends on the type of database you are using. Unfortunately every vendor still has its own interpretation of the ANSI SQL syntax

I don't think you need to loose the aliases altogether. You're probably alright if you just remove the quotes around them, and replace the spaces by underscores.

Geert