How to fix CQL syntax for multiple criteria?

user_6662421 0
Viewed 58

I have below query, where I want to filter out records using multiple criteria. But I am getting below syntax error.

Query

SELECT * FROM mydb.test 
where org=123 
AND (status = 'over' AND ecode = 196) 
OR (status = 'start' AND ecode = 195) 
ALLOW FILTERING;

Syntax error

SyntaxException: <Error from server: code=2000 [Syntax error in CQL query] 
message="line 1:88 mismatched input 'AND' expecting ')' (... AND (status = 'over' [AND]...)">

How can I fix this syntax error?

cql nosql cassandra database
1 Answers

OR is not supported by Cassandra...

Alex is correct. Cassandra does not support the OR keyword. It's one of the differences between CQL and SQL. In fact, given Cassandra's storage model, an OR construct is particularly problematic.

How can I achieve this scenario?

I can think of a few ways.

With Cassandra, the general idea with data modeling is to build your tables to suit your queries. So the first, would be to apply the logic on the data load, but your logic may be too complex for that.

You could also split this query into two queries (based on your AND conditions) and process the result sets on the application side. Not optimal, but it might be the only way to get the fine-grained control you need.

The other approach, would be to try using IN to get around the absence of OR. Just be careful not to restrict your partition key with IN, and always specify your partition key (with an = operator) when you do. That way you'll limit your query to processing on a single node. In fact, using IN on a clustering key (again, with = on your partition key) is really the only way I would recommend its use in a production system.

user_10545580
Related