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,
Rams
