There seems to be something wrong with the query (the red highlighted portion). What is mi in "abs(datediff(mi"?
On Sun, Jul 22, 2012 at 11:28 AM, Techy Teck <comptechge...@gmail.com> wrote: > Can anyone give some sort of pointers or suggestions why the below query is > giving me exception and the same query works fine in SQL server. Below is > the SQL fiddle that works in SQL Server- > > http://sqlfiddle.com/#!3/d87b2/4 > > > > On Fri, Jul 20, 2012 at 6:47 PM, Techy Teck <comptechge...@gmail.com> wrote: >> >> In hive we cannot calculate the difference between dates in minutes? >> >> >> >> >> On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <comptechge...@gmail.com> >> wrote: >>> >>> Whenever I am running the below query >>> >>> SELECT TT.BUYER_ID , COUNT(*) FROM >>> (SELECT testingtable1.buyer_id, testingtable1.item_id, >>> testingtable1.created_time from (select user_id, prod_and_ts.product_id as >>> product_id, prod_and_ts.timestamps as timestamps from testingtable2 LATERAL >>> VIEW explode(purchased_item) exploded_table as prod_and_ts where >>> to_date(from_unixtime(cast(prod_and_ts.timestamps as BIGINT))) = >>> '2012-07-09') prod_and_ts RIGHT OUTER JOIN (SELECT buyer_id, item_id, >>> rank(buyer_id), created_time, UNIX_TIMESTAMP(created_time) >>> FROM ( >>> SELECT buyer_id, item_id, created_time >>> FROM testingtable1 >>> where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) = >>> '2012-07-09' >>> DISTRIBUTE BY buyer_id >>> SORT BY buyer_id, created_time desc >>> ) a >>> WHERE rank(buyer_id) < 5) testingtable1 ON (testingtable1.item_id = >>> prod_and_ts.product_id AND testingtable1.BUYER_ID = prod_and_ts.USER_ID AND >>> abs(datediff(mi, >>> testingtable1.created_time,FROM_UNIXTIME(cast(prod_and_ts.timestamps as >>> BIGINT)))) <= 15) where prod_and_ts.product_id IS NULL ORDER BY >>> testingtable1.buyer_id, testingtable1.created_time desc) TT GROUP BY >>> TT.BUYER_ID; >>> >>> >>> I am getting below exception as - Its happening in red color in above >>> query >>> >>> FAILED: Error in semantic analysis: line 10:157 Invalid Table Alias mi. >>> >>> But the same thing works fine in SQL Server. Anything wrong I am doing in >>> the red line? I am currently trying to see by that red line is if difference >>> between date is within 15 minutes. >> >> >
