SQLTeam.com | Weblogs | Forums

3-level IF statement


#1

I have 3 objects in a hierarchy, customer1, customer2 and customer3. I need to write an IF statement that states -
If customer1 is null or = '?' then customer2. If the output of that statement (i.e. customer2) is null or = '?' then customer3.

So customer1 is the priority if populated, then customer2, then lastly customer3 if neither 1 or 2 are populated.

Thanks


#2
COALESCE(NullIf(Customer1, '?')
         , NullIf(Customer2, '?')
         , NullIf(Customer3, '?')
)

#3

Try this

create table #test
(
customer1 varchar(10) null,
customer2 varchar(10) null,
customer3 varchar(10) null,
)

insert into #test values (Null,Null,'c3'),(Null,'c2','?'),('c1',Null,'?'),('?','c2',Null)

select customer1,customer2,customer3, iif(isnull(customer1,'?') ='?',iif(isnull(customer2,'?') ='?',customer3,customer2),customer1) from #test