### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 4:44pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/1 "2018-02-02T16:44:29Z")
how can I implement these sql conditions in elastic ?
select \* from \
where ACCTTYPE\_ACCT\_TYPE\_CODE != '07'
and PLAZA\_PLAZA\_ID in ('009500', '009502')
and emp\_emp\_code != '9985'
and PURSTAT\_PUR\_STATUS\_CODE IN ( '08','09','10' )
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 5:38pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/2 "2018-02-02T17:38:18Z")
You can use a bool query with some filter and must\_not clauses.
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 5:43pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/3 "2018-02-02T17:43:09Z")
> [@aliyesami](#):
>
> where ACCTTYPE\_ACCT\_TYPE\_CODE != '07'
>
> and PLAZA\_PLAZA\_ID in ('009500', '009502')
can you kindly give me an example for "!=" and "IN" conditions ?
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 7:49pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/4 "2018-02-02T19:49:33Z")
You have a must not example here: [https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-bool-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-bool-query.html)
For IN I'd use: [https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-terms-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-terms-query.html)
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 7:49pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/5 "2018-02-02T19:49:34Z")
I tried for example the following for "PURSTAT\_PUR\_STATUS\_CODE IN ('08','09','10') "
but it select no rows ?
{
"query": {
"bool": {
"must": [
{"match": {"purstat\_pur\_status\_code.keyword": "08"}},
{"match": {"purstat\_pur\_status\_code.keyword": "09"}},
{"match": {"purstat\_pur\_status\_code.keyword": "10"}}
]
}
}
}
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 7:50pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/6 "2018-02-02T19:50:17Z")
Please format your code using `` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21) and not the citation button. It will make your post more readable.
Or use markdown style like:
````
```
CODE
```
````
Please edit your post.
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 7:50pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/7 "2018-02-02T19:50:48Z")
It's because it's a AND as you wrote it.
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 7:55pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/8 "2018-02-02T19:55:58Z")
I tried this but its not working , giving syntax error
```
GET _search
{
"query": {
"terms": {
"purstat_pur_status_code.keyword": ["08","09","10"]
},
"bool": {"must_not": [
{"match": {"accttype_acct_type_code.keyword": "07"}}
]}
}
}
```
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 8:05pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/9 "2018-02-02T20:05:41Z")
is the formatting ok now? I get the following error when I run the code I posted
```
{
"error": {
"root_cause": [
{
"type": "parsing_exception",
"reason": "[terms] malformed query, expected [END_OBJECT] but found [FIELD_NAME]",
"line": 6,
"col": 6
}
],
"type": "parsing_exception",
"reason": "[terms] malformed query, expected [END_OBJECT] but found [FIELD_NAME]",
"line": 6,
"col": 6
},
"status": 400
}
```
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 8:19pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/10 "2018-02-02T20:19:45Z")
Put the terms query inside the bool query in a must clause.
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 8:34pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/11 "2018-02-02T20:34:55Z")
its a valid json but getting STATUS :400 error parsing exception
```
GET purchase/_search
{
"query": {
"bool": {"must_not": [
{"match": {"accttype_acct_type_code.keyword": "07"}}
],
"terms": {
"purstat_pur_status_code.keyword": ["08","09","10"]
}
}
}
}
```
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 8:55pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/12 "2018-02-02T20:55:43Z")
Look at the bool query doc I linked to.
A bool clause can only have `should`, `must` and `must_not`.
So put the `terms` **inside** the `must` clause.
Just like you did for `match` inside `must_not`.
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 8:57pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/13 "2018-02-02T20:57:02Z")
is this good ?
```
GET purchase/_search
{
"query": {
"bool": {
"must_not": [
{"match": {"accttype_acct_type_code.keyword": "07"}}
],
"must": [
{
"terms": {
"purstat_pur_status_code.keyword": ["08","09","10"]
}
}
]
}
}
}
```
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 9:06pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/14 "2018-02-02T21:06:29Z")
It looks good
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 9:12pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/15 "2018-02-02T21:12:54Z")
thanks for your patience , I am almost there . how do I add the new " and PLAZA\_PLAZA\_ID in ('009500', '009502')" term to the elastic query ? will it go in the must clause with two "terms" ?
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 9:24pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/16 "2018-02-02T21:24:01Z")
Correct
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 9:26pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/17 "2018-02-02T21:26:55Z")
I tried this but its giving parsing error
```
GET purchase/_search
{
"query": {
"bool": {
"must_not": [
{"match": {"accttype_acct_type_code.keyword": "07"}}
],
"must": [
{
"terms": {
"purstat_pur_status_code.keyword": ["08","09","10"]
},
"terms": {
"plaza_plaza_id.keyword":["009500","009502"]
}
}
]
}
}
}
```
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 9:32pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/18 "2018-02-02T21:32:49Z")
I think I got it right . .?
```
GET purchase/_search
{
"query": {
"bool": {
"must_not": [
{"match": {"accttype_acct_type_code.keyword": "07"}}
],
"must": [
{"terms": {"purstat_pur_status_code.keyword": ["08","09","10"]}},
{"terms": {"plaza_plaza_id.keyword":["009500","009502"]}}
]
}
}
}
```
---
### Author:  [@aliyesami](https://discuss.elastic.co/u/aliyesami)
#### Post date: [February 2, 2018, 9:38pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/19 "2018-02-02T21:38:43Z")
I need to add a date range clause to it , how do I do that ?
```
And PUR_TRANS_DATE between to_date('13-JAN-2015 00:00:00','dd-mon-yyyy hh24:mi:ss') AND to_date('13-JAN-2015 23:59:59','dd-mon-yyyy hh24:mi:ss')
```
the following code gives me a date range but how do I add to the existing elasticsearch code?
```
"query": {
"range":{
"pur_trans_date":{
"gte":"2015-01-01T05:00:02.000Z",
"lte":"2015-01-01T10:00:02.000Z",
"format":"yyyyMMdd'T'HHmmss.SSSZ"
}
}
}
```
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [February 2, 2018, 9:40pm UTC](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255/20 "2018-02-02T21:40:26Z")
Exact
[Next page](https://discuss.elastic.co/t/how-to-implement-this-in-elastic/118255.md?page=2)