I have got strange error strange error when doing query and ordering result. I have use this tutorial SQL++ Query Strings | Couchbase Docs
Query that I use is:
SELECT channels, documentType, meta().id, name, no, workOrderItemId from _ where documentType = ‘Unit’ and meta().id in (“5c8088ef-e0d7-4f64-9a75-b383e18550f3”,“09480290-215b-4744-8212-ec0bdeca2012”) ORDER BY name ASC
Error that i got is:
N1QL syntax error near character 204
(CouchbaseLite Android v3.0.0-192@31 (EE/release, Commit/4769f18387@7451a45c924d Core/3.0.0 (192) at 2022-01-27T02:44:15.135497Z) on Java; Android 9; Logic Instrument Fieldbook N101)
Any idea what is wrong with that query?
The syntax for IN is [ ] not () - it is an array in N1QL. So:
SELECT channels, documentType, meta().id, name, no, workOrderItemId from _
where documentType = 'Unit'
and meta().id in ["5c8088ef-e0d7-4f64-9a75-b383e18550f3","09480290-215b-4744-8212-ec0bdeca2012"]
ORDER BY name ASC
should work for you.
( Ref: Collection Operators | Couchbase Docs )
I’m not sure if it was clear that we are talking about N1QL on android.
In the documentation are (). You can see it in SQL++ Server Differences for feature use KEYS
(link: SQL++ Differences | Couchbase Docs)
So my syntax should be ok
My apologies. I do see parentheses in the SQL Lite syntax diagrams. I can’t see another syntactic error in your statement, and with the change to square brackets (and regular quotes) the statement parses on the server. (I do think the “N1QL for Couchbase Server” column has errors in it as I see parentheses for the USE KEYS clause when square brackets are required.)
That said, have you tried with square brackets to confirm the syntax error then changes?
Yes, error message in this case is different. In this case is N1QL syntax error near character 121
We also met that strange behaviour of syntax error in cblite cli 3.1.0 using IN clause and ORDER BY in the same request.
To fix it, we used instead of IN clause, an
I have same answer . thanks for this great discussion