Sql query giving null ouput

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

@Christian_Dahlqvist can you help please

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.

2 Likes

try this

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
            """
}
1 Like

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

Then I would recommend upgrading to Elasticsearch 7.10. :slight_smile:

1 Like