hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From siva kumar <siva165...@gmail.com>
Subject Inline query in select clause
Date Mon, 21 Jul 2014 10:22:41 GMT
Hi,
I have below 2 tables�
1) create table dim (rank string,grade string) row format delimited fields
terminated by ',' stored as textile;
Data:
1,1
2,1
3,1
4,2
5,2
6,2
7,3
2) create table fact (rollno string,name string,sub1 string,rank1
string,sub2 string,rank2 string,sub3 string,rank3 string) row format
delimited fields terminated by ',' stored as textile;
Data :
1,name1,english,1,maths,2,science,3
2,name2,english,5,maths,6,science,7
3,name1,english,7,maths,4,science,1
Below is the oracle query for which an equivalent query in HIVE is needed.
Is it possible to do without using UDF?
The requirement display the grade instead of rank in the fact table�.
select a.rollno,a.name,a.sub1,(select grade from dim where rank = a.rank1),
                                    a.sub2,(select grade from dim where
rank = a.rank2),
                                    a.sub3,(select grade from dim where
rank = a.rank3)
from fact;
Note : For simplicity have kept limited range/rows, actual requirement has
a very big range which cannot be hardcoded.
regards,
siva

Mime
View raw message