hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "N. Ramasubramanian" <ramasubramanian.naraya...@gmail.com>
Subject inline query in the select clause
Date Fri, 11 Jul 2014 19:23:01 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,
Rams
Mime
View raw message