I have this below query, whenever I try to execute it, I always get error,
which I am not able to understand.

SELECT
   t1.buyer_id,
   t1.item_id,
   t1.created_time,
   t2.product_id,
   t2.timestamps
FROM
   TestingTable1 t1
   JOIN
   (
   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
   ) t2
   ON(t1.buyer_id = t2.user_id)
WHERE
   (t1.item_id <> t2.product_id)
   OR (unix_timestamp(t1.created_time) <> t2.timestamps);
I always get error as-

*FAILED: Error in semantic analysis: line 13:6 Invalid Table Alias or
Column Reference prod_and_ts*
*Raihan Jamal*

When you say
LATERAL VIEW explode(purchased_item) exploded_table as prod_and_ts
The last alias is an alias to a column; not a table. explode will only
produce one column per row; it cannot produce multiple columns. I hope
that helps.

On Tue, Jul 10, 2012 at 8:31 PM, Raihan Jamal <[EMAIL PROTECTED]> wrote:
> I have this below query, whenever I try to execute it, I always get error,
> which I am not able to understand.
>
> SELECT
>    t1.buyer_id,
>    t1.item_id,
>    t1.created_time,
>    t2.product_id,
>    t2.timestamps
> FROM
>    TestingTable1 t1
>    JOIN
>    (
>    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
>    ) t2
>    ON(t1.buyer_id = t2.user_id)
> WHERE
>    (t1.item_id <> t2.product_id)
>    OR (unix_timestamp(t1.created_time) <> t2.timestamps);
>
>
> I always get error as-
>
> FAILED: Error in semantic analysis: line 13:6 Invalid Table Alias or Column
> Reference prod_and_ts
>
>
>
>
>
>
> Raihan Jamal
>

Whenever I try to run this below query, I always get error as OR is not
supported in JOIN? Is there any other way of doing this query?
   SELECT *
    FROM   TestingTable1 tt1 LEFT JOIN TestingTable2 tt2
    ON tt1.buyer_id     = tt2.user_id
  AND (tt1.item_id      = tt2.product_id
    OR unix_timestamp(tt1.created_time) = tt2.timestamps)
Any suggestions will be appreciated.
*Raihan Jamal*

The LEFT and the RIGHT JOIN is invalid, you need  LEFT OUTER JOIN or RIGHT
OUTER JOIN or just JOIN (no INNER JOIN)

On Fri, Jul 13, 2012 at 11:06 AM, Raihan Jamal <[EMAIL PROTECTED]>wrote:

> Whenever I try to run this below query, I always get error as OR is not
> supported in JOIN? Is there any other way of doing this query?
>
>
>    SELECT *
>     FROM   TestingTable1 tt1 LEFT JOIN TestingTable2 tt2
>     ON tt1.buyer_id     = tt2.user_id
>   AND (tt1.item_id      = tt2.product_id
>     OR unix_timestamp(tt1.created_time) = tt2.timestamps)
>
>
> Any suggestions will be appreciated.
>
>
> *Raihan Jamal*
>
>