SQL Query to list relationships in HTML report

[Steve G]

Hi all,

I have tried to create an SQL query to list Relationships as a table with source and target mapping to the elements table.

SELECT R.id, R.type, R.name, R.documentation, E_Source.name as source, E_target.name as target FROM Relationships AS R,   Elements AS E_Source, Elements AS E_target WHERE R.sourceid = E_Source.id AND R.targetid = E_target.id

When I run this in the HTML report, I get no output and no error either. I created a small Archimate model that has the fields populated in the individual tables that show up when you insert Select * from Relationships and Select * from Elements, etc.

I was also trying to create a query to filter relationships of a tagged view. Which requires multiple concatenated JOIN/UNION statements followed by a WHERE clause for the properties table. But whenever I try to run a complex query it gives me no output and no error code.

Any idea why this is happening in the HTML report?

Thanks in advance!

[Jean-Baptiste Sarrodie]

Hi,

'target' is a reserved word of the SQL engine. Just use anything instead and it will work:

Code Select Expand

SELECT R.id, R.type, R.name, R.documentation, E_Source.name as source, E_target.name as tgt FROM Relationships AS R,   Elements AS E_Source, Elements AS E_target WHERE R.sourceid = E_Source.id AND R.targetid = E_target.id

Syntaxe errors are visible on the console, so when designing a new query it is useful to open your browser built-in "inspector" or "web developer tool" (usually by pressing F12).

Btw, my experience with this query engine shows that you'll get better performances by using the ANSI/ISO join syntax.

Regards,

JB