CODEWITHSUNDEEP
hivehiveql
Sekhar
Sekhar

Reputation: 699

Extract ang join from same column in Hive

I have a requirement to have different data from single column from another table.

Lets say I have a TAB_A with below sets of columns. enter image description here

TAB_B has one column.

enter image description here

My expected out put should be, enter image description here

Here col_x should have the data when code(TAB_B) = '08' and col_y should have the data when code(TAB_B) = '36'.

Whatever I have tried is not working,

SELECT 
a.col1, 
a.col2, 
a.col3, 
a.col4, 
substr(a.col5,13,3) as col_x,
substr(a.col5,13,3) as col_y
from TAB_A a 
JOIN TAB_B b on b.code = '08'
JOIN TAB_B b on b.code = '36'

Can anyone please suggest the best solution.

Upvotes: 0

Views: 52

Answers (1)

Strick
Strick

Reputation: 1642

You requirement is not pretty clear but seems you are looking for cross join and case when clause

SELECT 
a.col1, 
a.col2, 
a.col3, 
a.col4, 
case when code(TAB_B) = '08' then substr(a.col5,13,3)  else null end as col_x,
case when  code(TAB_B) = '36'then substr(a.col5,13,3)  else null end as col_y,
from TAB_A a 
JOIN TAB_B

;

Upvotes: 1

Related Questions