# Sql query giving null ouput

**URL:** <https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [November 19, 2020, 8:19am UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945 "2020-11-19T08:19:49Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![rohitarorait82](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rohitarorait82/32/82981_2.png) [@rohitarorait82](https://discuss.elastic.co/u/rohitarorait82)\
**Post date:** [November 19, 2020, 8:19am UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/1 "2020-11-19T08:19:50Z")

</div>

Hi All,

I am stuck with sql query output, can anyone please help

```
POST _sql?format=txt
{
 "query": "SELECT SERVICE_NAME,CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN count(*) END AS PUBLISHTRB,CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN count(*) END AS TOTALTRB,CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN count(*)ELSE 0 END - CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN count(*) ELSE 0 END AS SUPPRESSTRB FROM \"iib-eai-remo:temp*\" WHERE APPNAME LIKE '%TRB%' AND SERVICE_NAME = 'CANSUB' AND \"@timestamp\" >= NOW() - INTERVAL 1 HOURS AND (CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN 1 END IS NOT NULL OR CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN 1 END IS NOT NULL ) GROUp BY SERVICE_NAME,MSA_STATUS.keyword,MSA_FLOW_DIR.keyword "
}

```

I am getting below response :

```
 SERVICE_NAME | PUBLISHTRB | TOTALTRB | SUPPRESSTRB  
---------------+---------------+---------------+---------------
CANSUB |620 |null |-620           
CANSUB |null |1039 |1039           

```

I was expecting below output

```
 SERVICE_NAME | PUBLISHTRB | TOTALTRB | SUPPRESSTRB  
---------------+---------------+---------------+---------------
CANSUB |620 |1039 | 419
```

---

<div class="post-metadata">

**Author:** ![rohitarorait82](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rohitarorait82/32/82981_2.png) [@rohitarorait82](https://discuss.elastic.co/u/rohitarorait82)\
**Post date:** [November 19, 2020, 9:56am UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/2 "2020-11-19T09:56:06Z")

</div>

@Christian_Dahlqvist can you help please

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 19, 2020, 12:00pm UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/3 "2020-11-19T12:00:12Z")

</div>

This forum is manned by volunteers so please do not ping people not already involved in the thread. You also need to be patient. If you have not received any response after a few business days it is generally fine to ping for attention.

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [November 19, 2020, 1:24pm UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/4 "2020-11-19T13:24:12Z")

</div>

try this

```auto
POST _sql?format=txt
{
  "query": """
            SELECT SERVICE_NAME,
            ISNULL(SUM (CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN 1 ELSE 0 END), 0) AS PUBLISHTRB,
            ISNULL(SUM( CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN 1 ELSE 0 END), 0) AS TOTALTRB,
            (ISNULL(SUM (CASE WHEN MSA_STATUS.keyword ='PUBLISH' AND MSA_FLOW_DIR.keyword ='EAI - SCRM specific processing' THEN 1 ELSE 0 END), 0) - ISNULL(SUM( CASE WHEN MSA_STATUS.keyword ='REQUEST' AND MSA_FLOW_DIR.keyword ='TRB_TO_EAI' THEN 1 ELSE 0 END), 0)) AS SUPPRESSTRB
            FROM \"iib-eai-remo:temp*\"
            WHERE APPNAME LIKE '%TRB%' AND SERVICE_NAME = 'CANSUB' AND "@timestamp" >= NOW() - INTERVAL 1 HOURS 
            GROUP BY SERVICE_NAME
            """
}

```

---

<div class="post-metadata">

**Author:** ![rohitarorait82](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rohitarorait82/32/82981_2.png) [@rohitarorait82](https://discuss.elastic.co/u/rohitarorait82)\
**Post date:** [November 20, 2020, 6:46am UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/5 "2020-11-20T06:46:22Z")

</div>

@ylasri Thanks but this is working in v 7.10 but not in v7.7 elasticsearch

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 20, 2020, 7:44am UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/6 "2020-11-20T07:44:52Z")

</div>

Then I would recommend upgrading to Elasticsearch 7.10. 🙂

---

<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:** [December 18, 2020, 7:44am UTC](https://discuss.elastic.co/t/sql-query-giving-null-ouput/255945/7 "2020-12-18T07:44:58Z")

</div>

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