CODEWITHSUNDEEP
sqlhivehiveql
Pat Doyle
Pat Doyle

Reputation: 384

HiveQL - Joining on a column created on my Select Statement

I am trying to join two tables in Hive on multiple column. One of the columns I want to join on is a one where I am manipulating some data to match the structure in the other table and then I plan to join on it.

My challenge is that the column that I am creating will not allow me to join on it. I have tried a few different ways but I have been unsuccessful to this point. Any help is appreciated!

SELECT 
prvdr_num, 
CONCAT(SUBSTR(CLM_ADMSN_DT, 5, 2),"/",SUBSTR(CLM_ADMSN_DT, 7, 
2),"/",SUBSTR(CLM_ADMSN_DT, 1, 4)) as Calendar_Admit_date,
AdmitDate,
CMSId
FROM table1
INNER JOIN  table2
ON table1.CMSId = table2_lds.prvdr_num
AND table1.AdmitDate = 
table2.CONCAT(SUBSTR(CLM_ADMSN_DT, 5, 2),"/",SUBSTR(CLM_ADMSN_DT, 7, 
2),"/",SUBSTR(CLM_ADMSN_DT, 1, 4))

I have also tried this and it will not work. 

SELECT 
prvdr_num, 
CONCAT(SUBSTR(CLM_ADMSN_DT, 5, 2),"/",SUBSTR(CLM_ADMSN_DT, 7, 
2),"/",SUBSTR(CLM_ADMSN_DT, 1, 4)) as Calendar_Admit_date,
AdmitDate,
CMSId
FROM table1
INNER JOIN  table2
ON table1.CMSId = table2_lds.prvdr_num
AND table1.AdmitDate = 
table2.Calendar_Admit_date

Upvotes: 1

Views: 87

Answers (1)

Daniel Marcus
Daniel Marcus

Reputation: 2686

Have you tried to cast your new column as a date on the join? 

 SELECT 
    prvdr_num, 
    CONCAT(SUBSTR(CLM_ADMSN_DT, 5, 2),"/",SUBSTR(CLM_ADMSN_DT, 7, 
    2),"/",SUBSTR(CLM_ADMSN_DT, 1, 4)) as Calendar_Admit_date,
    AdmitDate,
    CMSId
    FROM table1
    INNER JOIN  table2
    ON table1.CMSId = table2_lds.prvdr_num
    AND table1.AdmitDate = 
    cast(CONCAT(SUBSTR(table2.CLM_ADMSN_DT, 5, 2),"/",SUBSTR(table2.CLM_ADMSN_DT, 7, 
    2),"/",SUBSTR(table2.CLM_ADMSN_DT, 1, 4)) as date)

Upvotes: 1

Related Questions