0

I'm new on Impala, and I'm trying to write a query where one field is SUM(X), and I want to put a condition on that field. When I used that query in MySQL, "HAVING SUM(X) IS NOT NULL" worked great - does anyone know what's the equivalent in Impala?

I tried a few ways, this is what I got:

  • "HAVING SUM(X) IS NOT NULL" --> AnalysisException: No matching function with signature: isnull(STRING).
  • "HAVING SUM(X) NOT NULL" --> AnalysisException: Syntax error in line 93: HAVING SUM(X) NOT NULL ^ Encountered: NULL Expected: BETWEEN, IN, LIKE, REGEXP, RLIKE CAUSED BY: Exception: Syntax error

If anyone has an answer for me I'd really appreciate it... Thank you!

Bramat
  • 979
  • 4
  • 24
  • 40

1 Answers1

-1

Did you try IF conditional function?

HAVING IF(SUM(X) = NULL, FALSE, TRUE)

Documentation: http://www.cloudera.com/documentation/archive/impala/2-x/2-1-x/topics/impala_conditional_functions.html

Vitalii Kotliarenko
  • 2,947
  • 18
  • 26
  • You should never compare directly with = against NULL. This might always yield false regardless of what SUM(X) actually is. For instance select `IF( NULL = NULL, 1, 0 )` yields 0 and not 1 as you would expect. On the other hand `select IF( NULL != NULL, 1, 0 )` also yields 0 and not the negation of the former.... `select if( NULL is NULL, 1, 0 ), if( NULL is not NULL, 1, 0 )` works as expected I am talking about the specific case of Impala, here. This is implementation independent, but the behaviour of Impala in this respect follows, the SQL standard, I believe. – Mateo Dec 15 '16 at 22:21
  • By the way, in Impala, a simple alternative to `IF( expr, false, true )` is simply `NOT( expr )` – Mateo Dec 15 '16 at 22:24