Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of
 ramasubramanian.narayanan@gmail.com designates 209.85.192.178 as permitted
 sender)
From: "N. Ramasubramanian" <ramasubramanian.narayanan@gmail.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
Subject: inline query in the select clause
Message-Id: <E86D180F-6322-4722-85C6-9B9C405EAF44@gmail.com>
Date: Sat, 12 Jul 2014 00:53:01 +0530
To: user@hive.apache.org
Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.2\))

Hi,

I have below 2 tables=85
=20
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=85.

select a.rollno,a.name,a.sub1,(select grade from dim where rank =3D =
a.rank1),
                                    a.sub2,(select grade from dim where =
rank =3D a.rank2),
                                    a.sub3,(select grade from dim where =
rank =3D 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=