Help With Transact SQL ISNULL column fuction with addtional if null then if column 2 <= value then 'text'

Question

Hello,

Looking for any help I can get, I've got a snippet of code where I need multiple "order of operations to happen" but continue to receive an error on my use of <=.

What I've written is:

ISNULL(state_customer, appended_state) as US_State

I have an additional column that shows distance in miles, what I need to do is combine my ISNULL function with "if US_State = null then if distance <= 100 then 'CA' else US_State.

Can anyone assist?

Answer 1

This sounds to me like:

CASE WHEN state_customer IS NOT NULL THEN state_customer
     WHEN distance <= 100 THEN 'CA'
     ELSE appended_state
END

Answer 2

Check this expression too: COALESCE(state_customer, appended_state, case when distance <= 100 then 'CA' end) as US_State.

Answer 3

Hi @Sullivan, Matthew ,

For this type of problem we recommend that you could post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

According to your limited information, you could refer below example and check whether it is helpful.

create table #t   
(  
state_customer varchar(20),  
appended_state varchar(20),  
distance int  
)  
  
insert into #t values  
('a','b',100),  
(null,'c',100),  
(null,null,100),  
(null,null,200)  
  
select case when ISNULL(state_customer, appended_state) IS NULL   
		then case when distance <= 100 then 'CA'  end    
		else ISNULL(state_customer, appended_state)  
		end as US_State from #t  

Output:

US_State  
a  
c  
CA  
NULL  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.