Hive - Match 2 columns in the Same table and insert to a NEW table

Question

I have 2 tables,

Current_table

New_table (intended output)

What I am trying to do is Match the 2 columns(Num and Temp_closed) in the Current_table and output to New_Table.

As a start I first tried to select all rows in Column Num thats not in Temp_Closed

SELECT * FROM Current_table WHERE temp_closed <> Num

Result came as "0"

The tried,

SELECT 
  CASE WHEN num =temp_closed
    THEN '1' 
    ELSE '0' 
  END 
  AS MyDesiredResult
FROM Current_Table

then everything was "0"

I want to know how I can SELECT any values that is NOT IN Temp_Closed column from the column Num

I looked up for many examples, All examples are how to do it in 2 tables not in the same table. Any help would be appreciated. Thanks

score 1 · Accepted Answer · answered Jul 17 '20 at 03:22

1

If I understand this correctly what you want to do is:

SELECT * FROM current_table WHERE temp_close NOT IN (SELECT Num FROM current_table)

answered Jul 17 '20 at 03:22

Allan Situma

Other way around but correct. Thanks for your help, SELECT * FROM current_table WHERE num NOT IN (SELECT temp_close FROM current_table) – YJG Jul 17 '20 at 03:55

score 0 · Answer 2 · answered Jul 17 '20 at 03:26

0

you should try below

select * from Current_Table ct, Current_Table ct2 where ct.Num <> ct2.Temp_Closed

answered Jul 17 '20 at 03:26

RAnjeet PApa

2 Answers2