Thanks Bejoy ! It worked. Sent from my iPhone
On Dec 4, 2011, at 3:10 AM, Bejoy Ks <bejoy...@yahoo.com> wrote: > 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 > > > >
