1

when 

SELECT * from hospital_basic 
LEFT JOIN hospital_clinical on hospital_basic.p_id=hospital_clinical.p_id;

It is working fine but when creating view it is giving error

CREATE VIEW hospital_view AS
SELECT * from hospital_basic LEFT JOIN 
hospital_clinical on hospital_basic.p_id=hospital_clinical.p_id;
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
Ravi Anand
  • 47
  • 1
  • 3
  • 12

1 Answers1

2

you need to put an alias for the columns that you are selecting, you cannot do select * because the column p_id will be duplicated for the table definition

CREATE VIEW hospital_view AS 
SELECT 
hospital_basic.p_id as hospital_basic_p_id,
hospital_clinical.p_id as hospital_clinical_p_id,
other columns   ...
from hospital_basic LEFT JOIN hospital_clinical on hospital_basic.p_id=hospital_clinical.p_id;
hlagos
  • 7,690
  • 3
  • 23
  • 41
  • so, if together both have 20 column, I have to write it all? – Ravi Anand Mar 09 '18 at 19:44
  • you can use regular expressions to exclude all but one. https://stackoverflow.com/questions/31666905/hive-udf-for-selecting-all-except-some-columns – hlagos Mar 09 '18 at 20:10