Thanks Viral. That was a good piece of info.COALESCE() was some thing new to me
as I'm not from a db background. I googled more on COALESCE() and found it
really good,However that didn't resolve my problem. I got it resolved by re
framing my query this way
and ((unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime
)) OR upper(R8.REMOVAL_DATETIME) = 'NULL')
I had to do an equality check for the string 'NULL' rather than a null check.
Any clues why I had to go this way.
I'm using SQOOP to import data to Hive tables from teradata environment first
then processing the same using Hive QL.
Regards
Bejoy.K.S
________________________________
From: Viral Bajaria <viral.baja...@gmail.com>
To: user@hive.apache.org
Cc: Bejoy Ks <bejoy...@yahoo.com>
Sent: Sat, March 5, 2011 1:17:43 AM
Subject: Re: Date function unix_timestamp() with input values null doen't work
as desired
Bejoy,
you should use COALESCE() whenever you are comparing values that could have
NULL
values.
-Viral
On Fri, Mar 4, 2011 at 7:13 AM, Bejoy Ks <bejoy...@yahoo.com> wrote:
Hi Everyone
> I'm facing an issue with hive on a relatively larger query which involves
>joins on six hive tables. My query is running fine without any errors, all the
>map reduce jobs run to completion but unfortunately it is not showing up any
>results. I tried debugging the query and to investigate the root cause, When i
>removed one of the last conditions from the query it is showing up results,
>this
>is the condition I removed
>and ((unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime
>))or R8.REMOVAL_DATETIME IS NULL)
>I have used a similar comparison operation within my working query on dates
>using the unix_timestamp method which is working as desired. But here it is
>not,
>I'm assuming this would be due to the fact that r8.removal_datetime has null
>values hence here the evaluation would happen against a valid unix stamp and
>null.
>ie (unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime
>))
>would inturn lead to valid timestamp<= NULL
>
>Has any one faced similar situations before? How can i get around this hurdle?
>Please advise.
>
>
>Regards
>Bejoy.K.S
>
>
>
>
