# \_sql/translate api translate incorect case when field

**URL:** <https://discuss.elastic.co/t/sql-translate-api-translate-incorect-case-when-field/372741>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [January 3, 2025, 10:46am UTC](https://discuss.elastic.co/t/sql-translate-api-translate-incorect-case-when-field/372741 "2025-01-03T10:46:23Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tung238](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tung238/32/140444_2.png) [@tung238](https://discuss.elastic.co/u/tung238)\
**Post date:** [January 3, 2025, 10:46am UTC](https://discuss.elastic.co/t/sql-translate-api-translate-incorect-case-when-field/372741/1 "2025-01-03T10:46:23Z")

</div>

I have a query like this and although /\_sql endpoint return correct results. /\_sql/translate only return fields that use in query, without calculation

```auto
POST /_sql/translate
{
  "query": "SELECT Broker,IssueCount,Splits,CASE WHEN LCASE(Broker) LIKE '%kgh%' THEN IssueCount/2 ELSE Splits END AS TungTran,DATEDIFF('hour', S3_3, Created) AS DateDiff FROM projects_dev",
  "fetch_size": 10
}

```

result from translate api:

```auto
{
  "size": 10,
  "_source": false,
  "fields": [
    {
      "field": "Broker"
    },
    {
      "field": "IssueCount"
    },
    {
      "field": "Splits"
    },
    {
      "field": "S3_3",
      "format": "strict_date_optional_time_nanos"
    },
    {
      "field": "Created",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

```

I expected case when field will be convert in to some painless script or something else. Please give any suggestion.

---

<div class="post-metadata">

**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\
**Post date:** [January 3, 2025, 12:08pm UTC](https://discuss.elastic.co/t/sql-translate-api-translate-incorect-case-when-field/372741/2 "2025-01-03T12:08:16Z")

</div>

Hi @tung238

Maybe it's some limitation of the SQL Translate API. If you want to write the script I suggest this doc: [How to write scripts | Elasticsearch Guide [8.17] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-using.html)