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.naraya...@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 manishd...@gmail.com
