CODEWITHSUNDEEP
mysql
Subramanian V
Subramanian V

Reputation: 1

Set a column value to be null based on value from another column

I'm preparing a select query that should set a field as null if certain value is present in another field of the same query.

select statement_id, prod_id, description, exp_type, 
        curr_amt as Digital_income, curr_amt as Physical income 
from table1 
where date(created) = 'somedate'

Here, the field exp_type will have values like "Digital", "Physical", "Download", "Stream".

I'm cloning curr_amt field as digital and physical. Most of the times both will have similar values only.

My requirement is if the exp_type is "Physical" I need the curr_amt as 'Digital_income'` to be null and visa versa.

Can anyone assist me in getting the desired output?

I tried using CASE, ISNULL and COALESCE but in vain

Upvotes: 0

Views: 87

Answers (1)

Barmar
Barmar

Reputation: 780879

Use IF().

select statement_id, prod_id, description, exp_type, 
        IF(exp_type = 'Digital', curr_amt, NULL) as Digital_income, 
        IF(exp_type = 'Physical', curr_amt, NULL) as Physical_income 
from table1 
where date(created) = 'somedate'

DEMO

Upvotes: 1

Related Questions