# SQL query in R2RML not working anymore in later version **URL:** \ **Category:** Support\ **Created:** [November 21, 2017, 12:16pm UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703 "2017-11-21T12:16:15Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![PaulZH](https://avatars.discourse-cdn.com/v4/letter/p/f07891/32.png) [@PaulZH](https://community.stardog.com/u/PaulZH)\ **Post date:** [November 21, 2017, 12:16pm UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/1 "2017-11-21T12:16:15Z")
Stardog server 5.0.5 jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc driver: sqljdbc42-6.0.8112.100.jar 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 ---
**Author:** ![jess](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/jess/32/10_2.png) [@jess](https://community.stardog.com/u/jess)\ **Post date:** [November 22, 2017, 4:32am UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/2 "2017-11-22T04:32:42Z")
Hi Paul. Will get back to you on these Wednesday. Jess ---
**Author:** ![PaulZH](https://avatars.discourse-cdn.com/v4/letter/p/f07891/32.png) [@PaulZH](https://community.stardog.com/u/PaulZH)\ **Post date:** [November 29, 2017, 9:27am UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/3 "2017-11-29T09:27:30Z")
Hi Jess, 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. ---
**Author:** ![jess](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/jess/32/10_2.png) [@jess](https://community.stardog.com/u/jess)\ **Post date:** [November 30, 2017, 6:29am UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/4 "2017-11-30T06:29:53Z")
Hi Paul, 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. Best, Jess ---
**Author:** ![PaulZH](https://avatars.discourse-cdn.com/v4/letter/p/f07891/32.png) [@PaulZH](https://community.stardog.com/u/PaulZH)\ **Post date:** [November 30, 2017, 7:37am UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/5 "2017-11-30T07:37:44Z")
Clear, Thank you very much for this clarification. Paul ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [December 8, 2017, 9:11pm UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/6 "2017-12-08T21:11:56Z")
Hi Paul, 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. Best Regards, Paul Jackson ---
**Author:** ![PaulZH](https://avatars.discourse-cdn.com/v4/letter/p/f07891/32.png) [@PaulZH](https://community.stardog.com/u/PaulZH)\ **Post date:** [December 11, 2017, 7:35am UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/7 "2017-12-11T07:35:30Z")
Hi Paul, Just tested this, but get now `An error occurred adding RDF to the index: 'SINGLE_VALUE' is not a recognized built-in function name.` ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [December 11, 2017, 4:31pm UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/9 "2017-12-11T16:31:56Z")
Hi Paul, 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. I’ll update you when we have a fix. Regards, -Paul ---
**Author:** ![PaulZH](https://avatars.discourse-cdn.com/v4/letter/p/f07891/32.png) [@PaulZH](https://community.stardog.com/u/PaulZH)\ **Post date:** [December 11, 2017, 4:43pm UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/10 "2017-12-11T16:43:42Z")
OK, thanks for this. Paul ---
**Author:** ![system](https://canada1.discourse-cdn.com/flex030/uploads/stardog/original/2X/e/ed66b48a616f505106a4acde3c9cee7e6da9bc67.svg) [@system](https://community.stardog.com/u/system)\ **Post date:** [December 25, 2017, 4:43pm UTC](https://community.stardog.com/t/sql-query-in-r2rml-not-working-anymore-in-later-version/703/11 "2017-12-25T16:43:45Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.