# How to write case statement in Where clause

**URL:** <https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220>\
**Category:** Elasticsearch\
**Created:** [August 28, 2019, 7:56pm UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220 "2019-08-28T19:56:17Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![navjeet.singh](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@navjeet.singh](https://discuss.elastic.co/u/navjeet.singh)\
**Post date:** [August 28, 2019, 7:56pm UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220/1 "2019-08-28T19:56:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [August 29, 2019, 7:00am UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220/2 "2019-08-29T07:00:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![navjeet.singh](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@navjeet.singh](https://discuss.elastic.co/u/navjeet.singh)\
**Post date:** [August 29, 2019, 12:19pm UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220/3 "2019-08-29T12:19:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [August 30, 2019, 10:14am UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220/4 "2019-08-30T10:14:07Z")

</div>

That explains it. `CASE WHEN` is not available in that version. [It was introduced in 7.2.0](https://www.elastic.co/guide/en/elasticsearch/reference/7.2/release-notes-7.2.0.html)... your only option is to upgrade.

---

<div class="post-metadata">

**Author:** ![navjeet.singh](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@navjeet.singh](https://discuss.elastic.co/u/navjeet.singh)\
**Post date:** [August 30, 2019, 6:10pm UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220/5 "2019-08-30T18:10:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 27, 2019, 6:10pm UTC](https://discuss.elastic.co/t/how-to-write-case-statement-in-where-clause/197220/6 "2019-09-27T18:10:14Z")

</div>

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