Simple SQL-join in a file-based repository

[Shounbourgh]

Hi,

i wantet to experiment a bit with the SQL-Query-based searches (e.g. the "Find in Project"-View) in my file based repository. But i ended up really fast by triing to join tables

Log says:
---
DAO.QueryDef [3135]

Syntaxfehler in JOIN-Operation.

Context:
   SQL: SELECT *

FROM (t_object join t_package on t_object.Package_ID = t_package.Package_ID)
---

If i remove the brakets, the error changes to DAO.QueryDef [3131]

Is it only possible to build and use more complex SQL-queries if the repository is managed as a DBMS?

Greetings
Erik

[Geert Bellekens]

You can build very complex queries that work both on .eap as SQL, but you'll need a lot of brackets.

Here's a complex example that works on .eap files and SQL Server

Code: [Select]

select distinct uc.ea_guid AS CLASSGUID, 'Usecase' AS CLASSTYPE, rq.Name as ApplicationRequirement, bp.Name as BusinessProcess,ba.Name AS BusinessActivity, null as FIS,uc.name as UseCase, d.Name as UsecaseDiagram
 from ((((((((((((t_package ucp
inner join t_diagram d on ucp.[Package_ID] = d.[Package_ID])
inner join t_diagramobjects dob on dob.[Diagram_ID] = d.[Diagram_ID])
inner join t_object uc on (uc.[Object_ID] = dob.[Object_ID]
                          and uc.[Object_Type] = 'UseCase'))
inner join t_diagramObjects dobb on dobb.[Diagram_ID] = d.[Diagram_ID])
inner join t_object boundary on (boundary.[Object_ID] = dobb.[Object_ID]
                          and boundary.[Object_Type] = 'Boundary'))
inner join t_connector ucrq on ucrq.[Start_Object_ID] = uc.[Object_ID])
inner join t_object rq on (rq.[Object_ID] = ucrq.[End_Object_ID]
                         and rq.Object_Type = 'Requirement'))                        
inner join t_connector ucba on ucba.[Start_Object_ID] = uc.[Object_ID])
inner join t_object ba on (ucba.[End_Object_ID] = ba.[Object_ID]
                       and ba.[Object_Type] = 'Activity'
   and ba.[Stereotype] = 'Activity'))                       
inner join t_diagramObjects dobai on dobai.[Object_ID] = ba.[Object_ID])
inner join t_diagram dbp on dbp.Diagram_ID = dobai.Diagram_ID)
inner join t_object bp on (dbp.ParentID = bp.Object_ID
                         and bp.Object_Type = 'Activity'
and bp.Stereotype in ('BusinessProcess', 'Activity')))                                                                                               
where 1=1
and dob.[RectLeft] >= dobb.[RectLeft]
and dob.[RectLeft] <= dobb.[RectRight]
and dob.[RectTop]  <= dobb.[RectTop]
and dob.[RectTop]  >= dobb.[RectBottom]
union
select distinct uc.ea_guid AS CLASSGUID, 'Usecase' AS CLASSTYPE, rq.Name as ApplicationRequirement, bp.Name as BusinessProcess,null AS BusinessActivity, fis.name as FIS,uc.name as UseCase, d.Name as UsecaseDiagram
 from ((((((((((((((t_package ucp
inner join t_diagram d on ucp.[Package_ID] = d.[Package_ID])
inner join t_diagramobjects dob on dob.[Diagram_ID] = d.[Diagram_ID])
inner join t_object uc on (uc.[Object_ID] = dob.[Object_ID]
                          and uc.[Object_Type] = 'UseCase'))
inner join t_diagramObjects dobb on dobb.[Diagram_ID] = d.[Diagram_ID])
inner join t_object boundary on (boundary.[Object_ID] = dobb.[Object_ID]
                          and boundary.[Object_Type] = 'Boundary'))                       
inner join t_connector ucrq on ucrq.[Start_Object_ID] = uc.[Object_ID])
inner join t_object rq on (rq.[Object_ID] = ucrq.[End_Object_ID]
                         and rq.Object_Type = 'Requirement'))
inner join  t_connector fisrq on fisrq.[Start_Object_ID] = rq.[Object_ID])
inner join  t_object fis on (fisrq.[End_Object_ID] = fis.[Object_ID]
                       and fis.[Object_Type] = 'Class'
                       and fis.[Stereotype] = 'Message'))                                               
left join t_connectortag ct on (fis.ea_guid = ct.VALUE 
                                and ct.Property = 'MessageRef'))
left join t_connector c on (ct.ElementID = c.Connector_ID 
                           and c.stereotype = 'MessageFlow'))
left join t_diagramlinks dl on dl.ConnectorID = c.Connector_ID)   
left join t_diagram dbp on dbp.Diagram_ID = dl.DiagramID)
left join t_object bp on (dbp.ParentID = bp.Object_ID
                         and bp.Object_Type = 'Activity'
and bp.Stereotype in ('BusinessProcess', 'Activity')))                                                                                                 
where 1=1
and dob.[RectLeft] >= dobb.[RectLeft]
and dob.[RectLeft] <= dobb.[RectRight]
and dob.[RectTop]  <= dobb.[RectTop]
and dob.[RectTop]  >= dobb.[RectBottom]
order by 3,5,4   

[Shounbourgh]

Hey ty....i know my error.
I have to put the column-attributes in these "[]" brakets, if they contain non-alphanumeric characters...holy.

Thanks a lot for that fast reply - you opened up my eyes