Untested: SELECT a.c100, a.c300, b.c400 FROM t1 a JOIN t2 b ON a.c200 = b.c200 JOIN (SELECT DISTINCT a.c100 FROM t1 a2 JOIN t2 b2 ON a2.c200 = b2.c200 WHERE b2.c400 >= SYSDATE - 1) a3 ON a.c100 = a3.c100 WHERE b.c400 >= SYSDATE - 1 AND a.c300 = 0
________________________________ From: Raj Hadoop <hadoop...@yahoo.com> To: Hive <user@hive.apache.org> Sent: Wednesday, July 10, 2013 3:30 PM Subject: Oracle to Hive All, Can anyone give me tips on how to convert the following Oracle SQL to a Hive query. SELECT a.c100, a.c300, b.c400 FROM t1 a JOIN t2 b ON a.c200 = b.c200 WHERE a.c100 IN (SELECT DISTINCT a.c100 FROM t1 a JOIN t2 b ON a.c200 = b.c200 WHERE b.c400 >= SYSDATE - 1) AND b.c400 >= SYSDATE - 1 AND a.c300 = 0 The SYSDATE can be replaced by date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) in Hive. But I wanted to know the rest of the query. Any pointers or tips so that I can start on my own. Thanks in advance. Regards, Raj