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!
Hi,
'target' is a reserved word of the SQL engine. Just use anything instead and it will work:
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 (https://github.com/agershun/alasql/wiki/Join).
Regards,
JB