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 > > > >