SELECT
ObjectId, VersieId, GemeenteStatus,
(
SELECT Spelling
FROM Gemeentenaam
WHERE Gemeentenaam.GemeenteObjectId = Gemeente.ObjectId and Gemeentenaam.Taal=‘nl’
) AS Gemeentenaam_NDL,
(
SELECT Spelling
FROM Gemeentenaam
WHERE Gemeentenaam.GemeenteObjectId = Gemeente.ObjectId and Gemeentenaam.Taal=‘fr’
) AS Gemeentenaam_FRA,
(
SELECT Spelling
FROM Gemeentenaam
WHERE Gemeentenaam.GemeenteObjectId = Gemeente.ObjectId and Gemeentenaam.Taal=‘de’
) AS Gemeentenaam_DEU
FROM Gemeente
Getting as error:
field [Gemeentenaam_NDL] not found; input fields are: [ObjectId, VersieId, GemeenteStatus, $f0, $f00, $f05] term map=COLUMN(Gemeentenaam_NDL)@nl field [Gemeentenaam_FRA] not found; input fields are: [ObjectId, VersieId, GemeenteStatus, $f0, $f00, $f05] term map=COLUMN(Gemeentenaam_FRA)@fr field [Gemeentenaam_DEU] not found; input fields are: [ObjectId, VersieId, GemeenteStatus, $f0, $f00, $f05] term map=COLUMN(Gemeentenaam_DEU)@de
I suppose that in the past the query in the R2RML was sent as such to the SQL endpoint including non-standard SQL dialect features proper to the database used.
I have the impression that now the SQL used needs to be in the by Apache Calcite supported SQL variant and shockes on non standard language.
For virtual import we always send the query verbatim from the mapping. In contrast, SPARQL queries over the federated virtual graph currently require the SQL query to be parsed. We’re planning to relax this at the cost of some optimizations. The operators used in your other post, bitwise NOT (~) and bitwise XOR (^), are not recognized by the SQL grammar. While we support custom functions (via sql.functions), we aren’t able to do this with operators.
The query in this post is parsed but there’s a bug in our treatment of it whereby we aren’t able to reference the subquery fields. I have created issue #4481 for this and we’ll get it fixed in the December or January release.
I wanted to close the loop with respect to issue #4481 that Jess mentioned. This was included in release 5.1, which released today. Please give it a try at your convenience and let us know how it works out.
We’ve been able to recreate this error. I have created issue #4545 for it.
The issue is that Calcite converted the subquery into a join and, because it wasn’t able to guarantee it would return a single row, inserted this SINGLE_VALUE function, which isn’t supported on MS Sql. The test I ran when testing the last fix had a max() operator around the returned value of the subquery, so I missed this problem.