CODEWITHSUNDEEP
sqltemplatesenterprise-architect
Boggart
Boggart

Reputation: 1

Enterprise Architect - Complex Join in template

I currently have an issue with the custom sql of a fragment for my template. The code below works when creating the documentation on the project (Project X) I was testing this on however when I export then import it into another project (snapshot of Project X), I get issues. 

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note
FROM t_connector
LEFT JOIN t_object ON t_connector.end_Object_ID=t_object.Object_ID OR t_connector.Start_Object_ID=t_object.Object_ID
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

The first error was "Syntax error (missing operator) in query expression 't_connector.end_Object_ID=t_object.Object_ID OR t_connector.Start_Object_ID=t_object.Object_ID RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type'

Putting in brackets or editing the query to any of the following, 

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note
FROM ((t_connector
LEFT JOIN t_object ON t_connector.end_Object_ID=t_object.Object_ID OR t_connector.Start_Object_ID=t_object.Object_ID)
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type)
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note
FROM t_connector
LEFT JOIN t_object ON t_connector.end_Object_ID=t_object.Object_ID OR t_connector.Start_Object_ID=t_object.Object_ID
UNION
SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note
FROM t_connector
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note, TO1.Name, TO2.Name
FROM t_connector
LEFT JOIN t_object AS TO1 ON t_connector.end_Object_ID=t_object.Object_ID 
LEFT JOIN t_object AS TO2 ON t_connector.Start_Object_ID=t_object.Object_ID
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note, TO1.Name, TO2.Name
FROM (((t_connector
LEFT JOIN t_object AS TO1 ON t_connector.end_Object_ID=t_object.Object_ID)
LEFT JOIN t_object AS TO2 ON t_connector.Start_Object_ID=t_object.Object_ID)
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type)
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note, TO1.Name, TO2.Name
FROM t_connector
LEFT JOIN t_object TO1 ON t_connector.end_Object_ID=t_object.Object_ID
LEFT JOIN t_object TO2 ON t_connector.Start_Object_ID=t_object.Object_ID
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note, TO1.Name, TO2.Name
FROM (((t_connector
LEFT JOIN t_object TO1 ON t_connector.end_Object_ID=t_object.Object_ID)
LEFT JOIN t_object TO2 ON t_connector.Start_Object_ID=t_object.Object_ID)
RIGHT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type)
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

results in a 'Join not supported' error message.

When these errors occur, the expected result(s)/elements do not appear in the documentation when generated. This is on Enterprise Architect 12.

Upvotes: 0

Views: 282

Answers (1)

qwerty_so
qwerty_so

Reputation: 36323

If you are targeting EAP, you have limitations with JOIN operations.

SELECT t_object.Name, t_object.Object_ID, t_object.Object_Type, t_object.Note
FROM ((t_connector
LEFT JOIN t_object ON t_connector.end_Object_ID=t_object.Object_ID OR t_connector.Start_Object_ID=t_object.Object_ID)
LEFT JOIN t_objecttypes ON t_objecttypes.Object_Type=t_object.Object_Type)
WHERE (t_connector.start_Object_ID = #OBJECTID# or t_connector.end_Object_ID = #OBJECTID#)
AND t_objecttypes.Object_Type='Actor'

would work (though I don't know if the result would be acceptable). RIGHT is not supported by Access.

Upvotes: 1

Related Questions