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 manishd207@gmail.com
 designates 209.85.214.66 as permitted sender)
MIME-Version: 1.0
Reply-To: dunanimanish@gmail.com
In-Reply-To: 
 <CAHxBGfJjTNc8vkyT7YaAh0Uqu95fYrZdMKegpJoAoCK+65toAQ@mail.gmail.com>
References: 
 <CAHxBGfJjTNc8vkyT7YaAh0Uqu95fYrZdMKegpJoAoCK+65toAQ@mail.gmail.com>
Date: Fri, 26 Jul 2013 14:23:20 +0530
Message-ID: 
 <CABDWF25bNw-_9XOFrMwdVpPKxWxNcUqfNz=KHrQXwYFqJ-6GXg@mail.gmail.com>
Subject: Re: Need help in joining 2 tables
From: manish dunani <manishd207@gmail.com>
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=14dae9c0912c43e89f04e266467f

--14dae9c0912c43e89f04e266467f
Content-Type: text/plain; charset=ISO-8859-1

Hi Rama,

Have a look !!

Both sql and hiveql looks similar..
try this one..

*How to create view??*
*
*
*Ans:*
*
*
create view v1 as
select m.Emp_id,
m.Name,
m.Dob,
a.address1,
a.address2,
a.address3,
a.address4,
a.city,
a.state,
a.zip,
a.country from EMP_MASTER m join EMP_ADDRESS a on (m.Emp_id=a.Emp_id)
where m.row_create_date=(select MAX(row_create_date) from EMP_MASTER where
Emp_id = m.Emp_id)
and a.row_create_date = (select MAX(row_create_date) from EMP_ADDRESS where
Emp_id = a.Emp_id)


On Fri, Jul 26, 2013 at 1:47 PM, Ramasubramanian Narayanan <
ramasubramanian.narayanan@gmail.com> wrote:

> Hi,
>
> Need to create a view in HIVE as per the below query (written in Oracle).
> Need help to provide the equivalent query in HIVE to create view.
>
>
> *EMP_ADDRESS*
>
> Emp_Id
> Address1
> Address2
> Address3
> Address4
> City
> State
> Pin
> Country
> Alternate_Address1
> Alternate_Address2
> Alternate_Address3
> Alternate_Address4
> Alternate_City
> Alternate_State
> Alternate_Pin
> Alternate_Country
> row_create_date
>
>
> *EMP_MASTER*
>
> Emp_Id
> Name
> DOB
> Department
> Sex
> FathersName
> row_create_date
>
>
> *View Query*
>
> select
> MAST.Emp_Id,
> MAST.Name,
> MAST.DOB,
> ADDR.Address1,
> ADDR.Address2,
> ADDR.Address3,
> ADDR.Address4,
> ADDR.City,
> ADDR.State,
> ADDR.Pin,
> ADDR.Country
> from EMP_MASTER MAST, EMP_ADDRESS ADDR
> where
> MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where
> Emp_Id = MAST.Emp_Id)
> and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS
> where Emp_Id = ADDR.Emp_Id)
>
>
> regards,
> Rams
>


-- 
MANISH DUNANI
-THANX
+91 9426881954,+91 8460656443
manishd207@gmail.com

--14dae9c0912c43e89f04e266467f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Rama,<div><br></div><div>Have a look !!</div><div><br><=
/div><div>Both sql and hiveql looks similar..</div><div>try this one..</div=
><div><br></div><div><b><u>How to create view??</u></b></div><div><b><u><br=
>
</u></b></div><div><b><u>Ans:</u></b></div><div><b><u><br></u></b></div><di=
v>create view v1 as</div><div>select m.Emp_id,</div><div>m.Name,</div><div>=
m.Dob,</div><div>a.address1,</div><div>a.address2,</div><div>a.address3,</d=
iv>
<div>a.address4,</div><div>a.city,</div><div>a.state,</div><div>a.zip,</div=
><div>a.country from EMP_MASTER m join EMP_ADDRESS a on (m.Emp_id=3Da.Emp_i=
d)</div><div>where m.row_create_date=3D(<span style=3D"font-family:arial,sa=
ns-serif;font-size:13px">select MAX(row_create_date) from EMP_MASTER where =
Emp_id =3D m.Emp_id)</span></div>
<div><span style=3D"font-family:arial,sans-serif;font-size:13px">and a</spa=
n><span style=3D"font-family:arial,sans-serif;font-size:13px">.row_create_d=
ate =3D (select MAX(row_create_date) from EMP_ADDRESS where Emp_id =3D a.Em=
p_id)</span></div>
<div><span style=3D"font-family:arial,sans-serif;font-size:13px"><br></span=
></div><div><font face=3D"arial, sans-serif">=A0</font></div></div><div cla=
ss=3D"gmail_extra"><br><br><div class=3D"gmail_quote">On Fri, Jul 26, 2013 =
at 1:47 PM, Ramasubramanian Narayanan <span dir=3D"ltr">&lt;<a href=3D"mail=
to:ramasubramanian.narayanan@gmail.com" target=3D"_blank">ramasubramanian.n=
arayanan@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div dir=3D"ltr">Hi,<div><br></div><div>Need=
 to create a view in HIVE as per the below query (written in Oracle). Need =
help to provide the equivalent query in HIVE to create view.</div>
<div><br><div><br></div><div><div><b><u>EMP_ADDRESS</u></b></div>
<div><br></div><div>Emp_Id</div><div>Address1</div><div>Address2</div><div>=
Address3</div><div>Address4</div><div>City</div><div>State</div><div>Pin</d=
iv><div>Country</div><div>Alternate_Address1</div><div>Alternate_Address2</=
div>

<div>Alternate_Address3</div><div>Alternate_Address4</div><div>Alternate_Ci=
ty</div><div>Alternate_State</div><div>Alternate_Pin</div><div>Alternate_Co=
untry</div><div>row_create_date</div><div><br></div><div><br></div><div>

<b><u>EMP_MASTER</u></b></div><div><br></div><div>Emp_Id</div><div>Name</di=
v><div>DOB</div><div>Department</div><div>Sex</div><div>FathersName</div><d=
iv>row_create_date</div><div><br></div><div><br></div><div><b><u>View Query=
</u></b></div>

<div><br></div><div>select=A0</div><div>MAST.Emp_Id,</div><div>MAST.Name,</=
div><div>MAST.DOB,</div><div>ADDR.Address1,</div><div>ADDR.Address2,</div><=
div>ADDR.Address3,</div><div>ADDR.Address4,</div><div>ADDR.City,</div><div>

ADDR.State,</div><div>ADDR.Pin,</div><div>ADDR.Country</div><div>from EMP_M=
ASTER MAST, EMP_ADDRESS ADDR</div><div>where</div><div>MAST.row_create_date=
 =3D (select max(row_create_date) from EMP_MASTER where Emp_Id =3D MAST.Emp=
_Id)</div>

<div>and ADDR.row_create_date =3D (select max(row_create_date) from EMP_ADD=
RESS where Emp_Id =3D ADDR.Emp_Id)</div></div><div><br></div><div><br></div=
><div>regards,</div><div>Rams</div></div></div>
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br>MANISH DUNAN=
I<br>-THANX<br>+91 9426881954,+91 8460656443<div><a href=3D"mailto:manishd2=
07@gmail.com" target=3D"_blank">manishd207@gmail.com</a><br></div>
</div>

--14dae9c0912c43e89f04e266467f--