Inline query in select clause

[siva kumar <si...@gmail.com>]

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

Fwd: Inline query in select clause

[siva kumar <si...@gmail.com>]

---------- Forwarded message ----------
From: siva kumar <si...@gmail.com>
Date: Mon, Jul 21, 2014 at 3:52 PM
Subject: Inline query in select clause
To: user@hive.apache.org


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