CODEWITHSUNDEEP
sqlhive
sharp
sharp

Reputation: 2158

hive: case when - 1 column used twice

I am trying to replace the dollar_amount values with the existing one with these criteria. Whenever status = 'Open' in period = 201604 and status = 'Active' in period = 201605, I want the dollar_amount to be replace with 10. From the SQL query below, can I use 1 column (status) twice on the case statement?

Here is the sample data: 

create table atable1 (
    id int,
    status string, 
    period int,
    dollar_amount decimal(18,2) );


insert into atable1 values (1234, "Open", "201604", 0);
insert into atable1 values (1234, "Active", "201605", 9.99);
insert into atable1 values (2222, "Open", "201604", 0);
insert into atable1 values (2222, "Active", "201605", 9.99);

The raw data looks like: 

id  status  period  dollar_amount
1234    Open    201604  0
1234    Active  201605  9.99
2222    Open    201604  0
2222    Active  201605  9.99

After the change, I want the data to look like: 

id      status  period  dollar_amount
1234    Open    201604  0
1234    Active  201605  10
2222    Open    201604  0
2222    Active  201605  10

Below is the query that I tried: 

select 
id, 
status,
period,
case when 
    (status = 'Open' and period = 201604) 
    and (status = 'Active' and period = 201605) 
then 10
else dollar_amount 
end dollar_amount_value

Upvotes: 0

Views: 486

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Your query is close. However, you need to change the when condition in the case expression to 

case when status = 'Open' and period = 201604 then 10
     when status = 'Active' and period = 201605 then 10
else dollar_amount 
end

The problem with the expression you have is that, you are checking for two values of status and period on one row, which always fails. So, you should split them into separate when conditions.

Upvotes: 1

Related Questions