How to write case statement in Where clause

Elastic Stack Elasticsearch
1

Hello Team , i am trying to run the below query but it gives me error as mismatch any idea what i am doing wrong.

SELECT
*
FROM
"chat_comment" c1
where
(c1."createdDate" >= '2019-08-27'
AND c1."createdDate" <= '2019-08-28')
AND case when c1.divisionId=63 then 'SA Production' else 'Admin' end = 'SA Production'

Error: 14:22:20 FAILED [SELECT - 0 rows, 0.019 secs] [Code: 0, SQL State: ] line 8:10: mismatched input 'when' expecting {, 'AND', 'BETWEEN', 'GROUP', 'HAVING', 'IN', 'IS', 'LIKE', 'LIMIT', 'NOT', 'OR', 'ORDER', 'RLIKE', '{LIMIT', '=', '<=>', NEQ, '<', '<=', '>', '>=', '+', '-', '*', '/', '%'}
SELECT
*
FROM
"chat_comment" c1
where
(c1."createdDate" >= '2019-08-27'
AND c1."createdDate" <= '2019-08-28')
AND case when c1.divisionId=63 then 'SA Production' else 'Admin' end = 'SA Production';

Thanks,
Navjeet

2

That's strange. What version of Elasticsearch is this?

3

it is 6.7.1 and i have similar SQL run in Oracle without any issues not sure what i am missing

4

That explains it. CASE WHEN is not available in that version. It was introduced in 7.2.0... your only option is to upgrade.

5

thanks it works fine in 7.3.1. so it is version problem

6

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