My answer would have been a subquery but temporary view sounds much better. Thanks for sharing, Bejoy!
----- Original Message ----- From: "Bejoy Ks" <bejoy...@yahoo.com> To: user@hive.apache.org Sent: Sunday, 4 December, 2011 6:10:10 AM Subject: Re: hive case and group-by statement Hi JJ The error message you are seeing now is because of your usage of groupby. You need to specify it as 'group by' , a space between group and by. Even if you specify it as 'group by' the query wont execute because of a limitation with GROUP BY in hive. It would give some parse error as 'Expression not in GROUP BY key'. For details refer the JIRA 2099 which is still open https://issues.apache.org/jira/browse/HIVE-2099 But here you can gracefully achieve your goal with the usage of a Temporary View, as given below CREATE VIEW temp_view_1 AS SELECT A, CASE WHEN B IN(1,2) THEN 'Type A' ELSE 'Type B' END AS B, C from table_a; SELECT * FROM temp_view_1 GROUP BY A,B,C; DROP VIEW temp_view_1; The creation and usage of views won't create much significant overhead on your query execution as well. Hope it helps !... Regards Bejoy.K.S From: Mapred Learn <mapred.le...@gmail.com> To: user@hive.apache.org Sent: Sunday, December 4, 2011 2:51 PM Subject: hive case and group-by statement Hi, I have a following smaple query: select A, CASE WHEN B IN(1,2) THEN 'Type A' ELSE 'Type B' END AS B, C from table_a groupby A, B, C; But when i run this query, it gives error: FAILED: Error in semantic analysis: Line 95:0 Invalid table alias or column reference entity This error is from B defined after 'AS' in CASE statement. how can I make this group-by work ? Thanks, -JJ
