How to concatenate a string and an integer inside a conditional function in Impala

Question

In hive

select concat("Positive", 123);

Positive123

select if("Positive" in ('Negative', 'No', 'Sub-zero'), 123, concat("Positive",123));

Positive123

But in Impala:

select concat("Positive", 123);

AnalysisException: No matching function with signature: concat(STRING, TINYINT).

Using CAST works:

select concat("Positive", cast(123 as string));

Positive123

But

select if("Positive" in ('Negative', 'No', 'Sub-zero'),
          123,
          concat("Positive", cast(123 as string))
          );

AnalysisException: No matching function with signature: if(BOOLEAN, TINYINT, STRING).

How can I concatenate a string and an integer inside a conditional function if in Impala?

Answer 1

This is the error you are facing.

No matching function with signature: if(BOOLEAN, TINYINT, STRING).

You need to convert everything to one single type of data type - like string. So, solution is to cast the true case to string as well.
So here is how you can rewrite the SQL.

select if("Positive" in ('Negative', 'No', 'Sub-zero'),
          cast(123 as string),  -- cast the true case to string.
          concat("Positive", cast(123 as string))
          );

Impala is very bad in converting data types automatically.