Major Description
When we execute below SQL it is failing in HIVE.
SELECT T3.facility_name AS Facility_Name,
Count(DISTINCT ORDERS_SRL.tc_order_id) AS Count_of_Orders,
SUM(order_line_item.order_qty) AS Order_Line_Quantity
FROM order_line_item ORDER_LINE_ITEM join orders ORDERS_SRL ON (order_line_item.order_id = ORDERS_SRL.order_id )
left outer join (facility T2 join facility_alias T3 ON T2.facility_id = T3.facility_id) ON (ORDERS_SRL.o_facility_id = T2.facility_id)
GROUP BY T3.facility_name;
Error --> Error: Error while compiling statement: FAILED: ParseException line 5:97 cannot recognize input near 'ON' 'ORDERS_SRL' '.' in expression specification (state=42000,code=40000)
Same above SQL will work if we re-write as below.
Working SQL
--------------------
SELECT TAB2.Facility_Name,TAB1.Count_of_Orders,TAB1.Order_Line_Quantity FROM (SELECT ORDERS_SRL.o_facility_id AS o_facility_id, Count(DISTINCT ORDERS_SRL.tc_order_id) AS Count_of_Orders, SUM(order_line_item.order_qty) AS Order_Line_Quantity FROM order_line_item ORDER_LINE_ITEM join orders ORDERS_SRL ON order_line_item.order_id = ORDERS_SRL.order_id GROUP BY ORDERS_SRL.o_facility_id) TAB1
left outer join
(SELECT T3.facility_name, T2.facility_id FROM facility T2 join facility_alias T3 ON T2.facility_id = T3.facility_id ) TAB2
ON TAB1.o_facility_id = TAB2.facility_id;