# Scripted field from sql api not working **URL:** \ **Category:** Elasticsearch\ **Created:** [October 27, 2020, 3:46pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464 "2020-10-27T15:46:41Z")\ **Posts on this page:** 8\ **Page:** 1
**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:** [October 27, 2020, 3:46pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/1 "2020-10-27T15:46:41Z")
Hi All, I recently started using sql from elasticsearch. i am trying to use below API POST \_sql?format=csv { "query": "SELECT SERVICE\_NAME,Success,count(_) From"my\_inde_" WHERE "@timestamp" \>= NOW() - INTERVAL 3 HOURS group by SERVICE\_NAME,Success" } where Success is my scripted field. However I am getting below error. { "error" : { "root\_cause" : [ { "type" : "verification\_exception", "reason" : "Found 1 problem\nline 1:21: Unknown column [Success]" } ], "type" : "verification\_exception", "reason" : "Found 1 problem\nline 1:21: Unknown column [Success]" }, "status" : 400 } Can someone please help ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 27, 2020, 9:38pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/2 "2020-10-27T21:38:36Z")
What are the documents? Please format your code, logs or configuration files 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 ``` ```` This is the icon to use if you are not using markdown format: ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/e/7e6e239431ec2d71cbf1beef741f2e93e7cc762c.jpg) There's a live preview panel for exactly this reasons. Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text. If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible. ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [October 29, 2020, 4:03pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/3 "2020-10-29T16:03:34Z")
> [@rohitarorait82](#): > > where Success is my scripted field. If `Success` was created with Kibana's scripted fields functionality, you won't be able to use it in SQL since those are accessible to Kibana only. However, you might potentially be able to compute that field as an SQL expression and potentially also group by that expression (or it's aliased name). Something along the lines of: ```auto SELECT SERVICE_NAME, some_field > 0 AS Success, count() FROM "my_index" WHERE "@timestamp" >= NOW() - INTERVAL 3 HOURS GROUP BY SERVICE_NAME, Success ``` ---
**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:** [October 30, 2020, 1:35pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/4 "2020-10-30T13:35:52Z")
> [@bogdan.pintea](#): > > ```auto > SELECT SERVICE_NAME, some_field > 0 AS Success, count() FROM "my_index" WHERE "@timestamp" >= NOW() - INTERVAL 3 HOURS GROUP BY SERVICE_NAME, Success > > ``` @bogdan.pintea : Thanks a lot it is almost working , I am getting below response. Can we print success/failure instead of true false. I am getting below output. Query : SELECT SERVICE\_NAME,ERRORSTATUS.keyword ='0' AS Success,count(_) FROM "my\_visualization_" WHERE "@timestamp" \>= NOW() - INTERVAL 1 HOURS and SERVICE\_NAME IN ('My\_service') group by SERVICE\_NAME,Success SERVICE\_NAME,Success,count(\*) SUB\_CHURN,false,1652 SUB\_CHURN,true,5 ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [October 30, 2020, 6:34pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/5 "2020-10-30T18:34:39Z")
> [@rohitarorait82](#): > > Can we print success/failure instead of true false. Have a look at the [CASE doc](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-conditional.html). Something like: `... CASE WHEN ERRORSTATUS.keyword ='0' THEN 'success' ELSE 'failure' END ...` ---
**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 13, 2020, 1:07pm UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/6 "2020-11-13T13:07:05Z")
> [@bogdan.pintea](#): > > CASE WHEN ERRORSTATUS.keyword ='0' THEN 'success' ELSE 'failure' Thanks @bogdan.pintea : Is there a way to find the difference of count of these two records ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [November 16, 2020, 8:08am UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/7 "2020-11-16T08:08:51Z")
> [@rohitarorait82](#): > > of count of these two rec You'll probably want something like `SELECT count(ERRORSTATUS.keyword) FROM .. GROUP BY ERRORSTATUS.keyword`. It's standard SQL, any good tutorial should touch on it. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 14, 2020, 8:08am UTC](https://discuss.elastic.co/t/scripted-field-from-sql-api-not-working/253464/8 "2020-12-14T08:08:59Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.