5

I need to query something like in AWS Athena

SELECT * FROM "hl"."may" where fqk = 'NaN' limit 10
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
jk1
  • 593
  • 6
  • 16

2 Answers2

6

Use is_nan:

select is_nan(nan());

 _col0
-------
 true
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
2

I was about to suggest is_nan... but it didnt work for me either as I tought it was specific to Python or Spark... However this worked for me.

SELECT * FROM "hl"."may" where cast(fqk as VARCHAR(4)) = 'NaN' limit 10

Wired604
  • 370
  • 1
  • 3
  • 10
  • Can you elaborate what didn't work with `is_nan` for you? Maybe your value wasn't a double? – Piotr Findeisen Jul 24 '18 at 05:44
  • 1
    Athena wasnt recognizing it... I got this error : Your query has the following error(s): Conversion = ''' (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: 7014cdc5-ab9b-46e2-938d-32c2de1df629)... But I know it works in other languages, I do use is_nan but in python... – Wired604 Jul 24 '18 at 13:48
  • Athena will not let me detect NaN. I have tried is_nan() and CAST(col as varchar). Nothing works. – Steve Gon Nov 02 '22 at 18:47