You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Teja Kunapareddy <te...@gmail.com> on 2015/06/03 08:23:45 UTC
Hive Query
Ravi,
Use if condition .. it works ..
Use ISNOTNULL instead, and give the replace field as third parameter for
if condition.
NOTE: false and null are together in if condition
If(ISNOTNULL(sum(columnname), sum(columnname),0) as sumVendor
Regards,
Teja
Re: Hive Query
Posted by João Alves <jo...@5dlab.com>.
Hey all,
Has anyone else also found the coalesce function to be prone to some weird behaviours?
e.g.1: Giving null when it shouldn’t.
e.g.2: I had to change a coalesce(v1,v2,v3) to coalesce(v1,v2,v3,null) (???) otherwise the query would crash!
Regards,
João Alves
> On 03 Jun 2015, at 17:00, gabriel balan <ga...@oracle.com> wrote:
>
> Hi
>
>> If(ISNOTNULL(sum(columnname), sum(columnname),0) as sumVendor
> Or
> coalesce( sum(columnname),0) as ...
> As explained here <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF>,
> COALESCE(T v1, T v2, ...) Returns the first v that is not NULL, or NULL if all v's are NULL.
>
>
> hth
> GB
>
> On 6/3/2015 2:23 AM, Teja Kunapareddy wrote:
>> Ravi,
>>
>> Use if condition .. it works ..
>>
>> Use ISNOTNULL instead, and give the replace field as third parameter for if condition…
>>
>>
>> NOTE: false and null are together in if condition
>>
>>
>> If(ISNOTNULL(sum(columnname), sum(columnname),0) as sumVendor
>>
>> Regards,
>> Teja
>>
>>
>
> --
> The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation.
Re: Hive Query
Posted by gabriel balan <ga...@oracle.com>.
Hi
> If(ISNOTNULL(sum(columnname), sum(columnname),0) as sumVendor
Or
*coalesce( sum(columnname),0) as ...*
As explained here <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF>,
COALESCE(T v1, T v2, ...) Returns the first v that is not NULL, or NULL if all v's are NULL.
hth
GB
On 6/3/2015 2:23 AM, Teja Kunapareddy wrote:
>
> Ravi,
>
> Use if condition .. it works ..
>
> Use ISNOTNULL instead, and give the replace field as third parameter for if condition…
>
> NOTE: false and null are together in if condition
>
> If(ISNOTNULL(sum(columnname), sum(columnname),0) as sumVendor
>
> Regards,
>
> Teja
>
--
The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation.