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