N1QL syntax error parameterized query

Mobile
, ,
1

Here is my query:

SELECT * AS data FROM Products WHERE Category LIKE CONCAT('%', $param0, '%') AND OrganizationId = $param1 ORDER BY Sku ASC LIMIT 1000 OFFSET 0

The error I get is:
N1QL syntax error near character 58.

Which is the begining of the CONCAT statement.

What simple thing am I missing here?

2

Mobile SQL++ differs from the server dialect, so I may be off the mark here, but I don’t see a CONCAT function listed here:

https://docs.couchbase.com/couchbase-lite/current/csharp/query-n1ql-mobile.html#lbl-func-string

I believe you may be able to achieve the concatenation using ‘||’ - i.e.

... WHERE Category LIKE '%' || $param0 || '%' ...

HTH.

1 Like
3 

SELECT * AS data 
FROM Products 
WHERE Category LIKE $param0  
              AND OrganizationId = $param1 
ORDER BY Sku ASC 
LIMIT 1000 
OFFSET 0

You can also include wildcards in $param0 (ex : “xyz”, “xyz%”, “%xyz”, “%xyz%”…)

5

I’m not a query or index expert, but if the indexes are sorted alphabetically - If you automatically prefix the search string with ‘%’, you will automatically be making the query much slower as it will require considering every value - which makes @vsr1’s suggestion valuable.

For instance - LASTNAME LIKE ‘%Sm%’ will cause the query engine to process every LASTNAME, whereas LASTNAME LIKE ‘Sm%’ would result in the query engine starting in the index at ‘Sm’ and stopping at the first LASTNAME that didn’t start with ‘Sm’.

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.