Error in Hive Query while joining tables
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins
Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job.
Try moving your inequalities to the where clause
select t1.*, t99.* from table1 t1 JOIN (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) where t3.visit_date between v3.start_dt and v3.end_dt and v3.adv_price <= t3.comp_price ) t99 ON (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);