hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Techy Teck <comptechge...@gmail.com>
Subject Re: Invalid Table Alias
Date Sat, 21 Jul 2012 01:47:48 GMT
In hive we cannot calculate the difference between dates in minutes?




On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <comptechgeeky@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.
>

Mime
View raw message